Source language: Translate to:

Date and DBP sum

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 7:44 am

Hello am doing POS Software
Where my table has the following fields
Item , Amount, and Date

And on the report designer I want the total item purchased to display based on the date the item was purchased.

So that gor example all items purchased on the
25/12/15
Will have a total below

So as
25/12/16

And so on. Please guyz help me on this. Thanx
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby stu » Thu Oct 22, 2015 7:55 am

Is unclear what your asking...

but anyway... What rdbms are you using?

On SQL Server:

Code: Select all
.For single date
dbpExecSQL "DB" "SELECT * FROM orders WHERE date = [#39]2015-12-25[#39]" ""
.Get total
dbpExecSQL "DB" "SELECT SUM(amout) AS TotalAmount FROM orders WHERE date = [#39]2015-12-25[#39]" "tempTotalAmount"
setvar "[TotalAmount]" "[DB.tempTotalAmount.TotalAmount]"
.Print total amount on report with [TotalAmount]
dbpPreviewReport "DB" "C:\OrdersReport.dbr" "DisplayMode=FitPage;InitialPage=1"
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 8:06 am

Pls rdms? I dont understand
But am using Mdb

Here is additional info
A user makes a sale by entering the item name, amount and date into the database.
And at the end of the week or month, the administrator comes to check for sales.

But this is the case where I want the sales to be displayed based on dates.

So that all items purchased within the same date will have its own total before the general total of the items.

I hope its clear now. Thanks
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby stu » Thu Oct 22, 2015 8:32 am

HackinHoodLogicsTm wrote:Pls rdms? I dont understand
But am using Mdb

Here is additional info
A user makes a sale by entering the item name, amount and date into the database.
And at the end of the week or month, the administrator comes to check for sales.

But this is the case where I want the sales to be displayed based on dates.

So that all items purchased within the same date will have its own total before the general total of the items.

I hope its clear now. Thanks


Its almost exactly the same on Access but to query dates instead of using single quote around the date you use hash/pound (#) and the local date format I think... like #22/10/2015#
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 8:55 am

Am confused now because the date will be input by users. So pls help me on how to continue.

My database name is
Offering
And have fields like
Offr_type
Offr_Amount
Offr_Date
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby stu » Thu Oct 22, 2015 9:00 am

Just change the dates for Variables to be populated by the user input.

Code: Select all
setvar "[DateVar]" "22/10/2015"
dbpExecSQL "DB" "SELECT * FROM orders WHERE date = #[DateVar]#" ""
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Date and DBP sum

Postby Gaev » Thu Oct 22, 2015 9:03 am

Its almost exactly the same on Access but to query dates instead of using single quote around the date you use hash/pound (#) and the local date format I think... like #22/10/2015#

Unfortunately, Access can only work with dates in US/American format (mm/dd/yyyy) ... so Stu's suggested code would be ...
Code: Select all
.For single date
dbpExecSQL "DB" "!SELECT * FROM orders WHERE date = [#39]12/25/2015[#39]" ""
.Get total
dbpExecSQL "DB" "!SELECT SUM(amout) AS TotalAmount FROM orders WHERE date = [#39]12/25/2015[#39]" "tempTotalAmount"
setvar "[TotalAmount]" "[DB.tempTotalAmount.TotalAmount]"
.Print total amount on report with [TotalAmount]
dbpPreviewReport "DB" "C:\OrdersReport.dbr" "DisplayMode=FitPage;InitialPage=1"
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 9:26 am

I get this error when I enter the codes
"Cannot perform this operation on a closed dataset"
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby stu » Thu Oct 22, 2015 9:31 am

Gaev wrote:
Its almost exactly the same on Access but to query dates instead of using single quote around the date you use hash/pound (#) and the local date format I think... like #22/10/2015#

Unfortunately, Access can only work with dates in US/American format (mm/dd/yyyy) ... so Stu's suggested code would be ...
Code: Select all
.For single date
dbpExecSQL "DB" "!SELECT * FROM orders WHERE date = [#39]12/25/2015[#39]" ""
.Get total
dbpExecSQL "DB" "!SELECT SUM(amout) AS TotalAmount FROM orders WHERE date = [#39]12/25/2015[#39]" "tempTotalAmount"
setvar "[TotalAmount]" "[DB.tempTotalAmount.TotalAmount]"
.Print total amount on report with [TotalAmount]
dbpPreviewReport "DB" "C:\OrdersReport.dbr" "DisplayMode=FitPage;InitialPage=1"


Cheers for the clarification... : )

HackinHoodLogicsTm wrote:I get this error when I enter the codes
"Cannot perform this operation on a closed dataset"


Demo db plz, or snip some screens of query, code and frontend
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 9:39 am

If possible I would want the Data to display this way on the Report sheet

Type Amount Date
1st Sevice 20 25/12/2015
2nd Service 20 25/12/2015
3rd Service 20 25/12/2015
[TotalAmoun]

1st Service 15 26/01/2016
2nd Service 45 26/01/2016
3rd Service 13 26/01/2016
[TotalAmount]

That is how I wanted to display the info so that the total amount will display after all items which fall within the same date. Thanks
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby stu » Thu Oct 22, 2015 10:46 am

The table:
Image

The Report:
Image

Calling the report:
Image

Code: Select all
dbpOpenDatabase "AccessSucks" "F:\test.mdb"
dbpOpenTable "AccessSucks" "tblOrders" ""
dbpExecSQL "AccessSucks" "SELECT OrderDate, ItemName |FROM tblOrders |WHERE OrderDate > #[DateStart]#" ""
dbpPreviewReport "AccessSucks" "F:\AccessSucks.dbr" "DisplayMode=100%;InitialPage=1"


Result:
Image
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 11:08 am

Code: Select all
.For single date
dbpExecSQL "Offr" "!SELECT * FROM orders WHERE date = [#34]12/25/2015[#34]" ""

.Get total
dbpExecSQL "Offr" "!SELECT * SUM(amount) AS TotalAmount FROM orders WHERE date = [#34]25/12/2015[#34] " "[Offr.Offr_Table.OffrAmount]"

SetVar "[TotalAmount]" "[Offr.Offr_Table.OffrAmount]"

.Print total amount on report with [TotalAmount]
dbpPreviewReport "Offr" "E:\BYONG\Projects\Neobook Source Codes\MaxTor CM\Reports\Temp.dbr" "DisplayMode=FitPage;InitialPage=1"


Here is a screen shot of the error
Image

And how i want it to display
Image
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: Date and DBP sum

Postby HackinHoodLogicsTm » Thu Oct 22, 2015 11:11 am

Thanks Bro. You are legend. It works Now. :D
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest