DSUM not working properly in report

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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# ")
 
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...
 
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...
 
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...
 
Back
Top