DSUM not working properly in report

G

Guest

Here's the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.Recordset

Set rs= CurrentDb.OpenRecordset("Select * From [MonthlyCash]",
dbOpenDynaset)
With rs
CashBal = DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05# ")
End With
MsgBox (CashBal) ' equals 500, SHOULD equal 1800 ??

Data in the Cash field of the [MonthlyCash] table:

11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

the DSUM should total 1800, but it totals 1300. Any ideas?

(1. I want to show the CashBal value in the report. 2 The MonthlyCash
table is not the table that the report is based on)
 
D

Duane Hookom

Why are you opening a recordset? You aren't using any values from rs in your
code.

It looks like you could get by with a text box on your report with a control
source of:
= DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05# ")
 
G

Guest

As you suggested, included the text box in a report with a control source of:

= DSum("[Cost]","[MonthlyCash]","[Sales Date] <= #" &
DateAdd("d",-(DatePart("d",[SalesDate])),[Sales Date]) & "#")

For example, if SalesDate 1/20/2006 is entered, the sum of all Cost for
dates less December 31, 2005 should be calculated (total all Cost before the
start of the entered month). HOWEVER, DSUM calculates 2000, but it should
total 2500. [see table below]

Another example, if SalesDate 12/11/2005 is entered, the sum of all Cost
for dates less November 30, 2005 should be calculated. Total should be 1500
(But DSUM calculates 700).

Data in the Cash field of the [MonthlyCash] table:

11/15/05 700
11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

(Seems to not include the last date in the range in the overall total)

Any ideas?

Duane Hookom said:
Why are you opening a recordset? You aren't using any values from rs in your
code.

It looks like you could get by with a text box on your report with a control
source of:
= DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05# ")

--
Duane Hookom
MS Access MVP
--

jsccorps said:
Here's the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.Recordset

Set rs= CurrentDb.OpenRecordset("Select * From [MonthlyCash]",
dbOpenDynaset)
With rs
CashBal = DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05#
")
End With
MsgBox (CashBal) ' equals 500, SHOULD equal 1800 ??

Data in the Cash field of the [MonthlyCash] table:

11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

the DSUM should total 1800, but it totals 1300. Any ideas?

(1. I want to show the CashBal value in the report. 2 The MonthlyCash
table is not the table that the report is based on)
Click to expand...
Click to expand...
 
D

Duane Hookom

I assume the space and lack of space in [Sales Date] is ok...

Try = DSum("[Cost]","[MonthlyCash]", "[Sales Date] <= #" &
DateSerial(Year([SalesDate]), Month([SalesDate]),0) & "#")
--
Duane Hookom
MS Access MVP
--

jsccorps said:
As you suggested, included the text box in a report with a control source
of:

= DSum("[Cost]","[MonthlyCash]","[Sales Date] <= #" &
DateAdd("d",-(DatePart("d",[SalesDate])),[Sales Date]) & "#")

For example, if SalesDate 1/20/2006 is entered, the sum of all Cost for
dates less December 31, 2005 should be calculated (total all Cost before
the
start of the entered month). HOWEVER, DSUM calculates 2000, but it should
total 2500. [see table below]

Another example, if SalesDate 12/11/2005 is entered, the sum of all Cost
for dates less November 30, 2005 should be calculated. Total should be
1500
(But DSUM calculates 700).

Data in the Cash field of the [MonthlyCash] table:

11/15/05 700
11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

(Seems to not include the last date in the range in the overall total)

Any ideas?

Duane Hookom said:
Why are you opening a recordset? You aren't using any values from rs in
your
code.

It looks like you could get by with a text box on your report with a
control
source of:
= DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05# ")

--
Duane Hookom
MS Access MVP
--

jsccorps said:
Here's the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)
Dim rs As DAO.Recordset

Set rs= CurrentDb.OpenRecordset("Select * From [MonthlyCash]",
dbOpenDynaset)
With rs
CashBal = DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <=
#12/31/05#
")
End With
MsgBox (CashBal) ' equals 500, SHOULD equal 1800 ??

Data in the Cash field of the [MonthlyCash] table:

11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

the DSUM should total 1800, but it totals 1300. Any ideas?

(1. I want to show the CashBal value in the report. 2 The MonthlyCash
table is not the table that the report is based on)
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The statement "Seems to not include the last date in the range in the
overall total" makes me suspect that Sales Date includes time, as well as
date. (In other words, it's been populated using Now(), not Date()).

If that's the case, try using

Try = DSum("[Cost]","[MonthlyCash]", "[Sales Date] < #" &
DateSerial(Year([SalesDate]), Month([SalesDate]),1) & "#")

although the purist in me suggests

Try = DSum("[Cost]","[MonthlyCash]", "[Sales Date] < "
Format(DateSerial(Year([SalesDate]), Month([SalesDate]),1),
"\#mm\/dd\/yyyy\#"))

just in case there's ever a chance the the user will have his/her short date
format set to dd/mm/yyyy.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Duane Hookom said:
I assume the space and lack of space in [Sales Date] is ok...

Try = DSum("[Cost]","[MonthlyCash]", "[Sales Date] <= #" &
DateSerial(Year([SalesDate]), Month([SalesDate]),0) & "#")
--
Duane Hookom
MS Access MVP
--

jsccorps said:
As you suggested, included the text box in a report with a control source
of:

= DSum("[Cost]","[MonthlyCash]","[Sales Date] <= #" &
DateAdd("d",-(DatePart("d",[SalesDate])),[Sales Date]) & "#")

For example, if SalesDate 1/20/2006 is entered, the sum of all Cost for
dates less December 31, 2005 should be calculated (total all Cost before
the
start of the entered month). HOWEVER, DSUM calculates 2000, but it
should
total 2500. [see table below]

Another example, if SalesDate 12/11/2005 is entered, the sum of all Cost
for dates less November 30, 2005 should be calculated. Total should be
1500
(But DSUM calculates 700).

Data in the Cash field of the [MonthlyCash] table:

11/15/05 700
11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

(Seems to not include the last date in the range in the overall total)

Any ideas?

Duane Hookom said:
Why are you opening a recordset? You aren't using any values from rs in
your
code.

It looks like you could get by with a text box on your report with a
control
source of:
= DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <= #12/31/05# ")

--
Duane Hookom
MS Access MVP
--

Here's the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)
Dim rs As DAO.Recordset

Set rs= CurrentDb.OpenRecordset("Select * From [MonthlyCash]",
dbOpenDynaset)
With rs
CashBal = DSum("[Cash]", "[MonthlyCash]", "[Sales Date] <=
#12/31/05#
")
End With
MsgBox (CashBal) ' equals 500, SHOULD equal 1800 ??

Data in the Cash field of the [MonthlyCash] table:

11/22/05 800
12/2/05 200
12/15/05 300
12/18/05 500
1/5/06 700
1/9/06 900
2/1/06 400

the DSUM should total 1800, but it totals 1300. Any ideas?

(1. I want to show the CashBal value in the report. 2 The
MonthlyCash
table is not the table that the report is based on)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSUM value shows up blank on report 1
DSUM Criteria in a Report 4
Multiple group levels and aggregate function for text field 2
Report with no data 3
total sum of quantity per item in a report 6
Using the function DSUM 7
How to calculate opening balance in a report 0
Running sum 3

Top