DSUM Date Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsccorps - 03 Feb 2006 11:28 GMT

Need help with formatting the DSUM date criteria. The following code is in
the Report Footer.

Set rs = CurrentDb.OpenRecordset("Select * From [TestCreditDebits]",
dbOpenDynaset)

' Is not pulling the date that is typed into the Form's SalesDate field.
' Seems to get some arbitrary date
' If I replace [SalesDate] with a constant date, it works fine

With rs
TotalSum = DSum
With rs
TotalSum = DSum("Credit", "TestCreditDebits", "[Sales Date] < #" &
[SalesDate] & "# ")
End With

Reply to this Message

Jeff Boyce - 03 Feb 2006 12:39 GMT

?[Sales Date] < [Sales Date]?

Which one is supposed to be the value from the domain, and which one is the
comparison/variable value?

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> Need help with formatting the DSUM date criteria. The following code is in
> the Report Footer.
[quoted text clipped - 12 lines]
> [SalesDate] & "# ")
> End With

Reply to this Message

SA - 03 Feb 2006 12:58 GMT

JSC:

1.) DSum does not work on record sets at all.
2.) You don't qualify the reference to [SalesDate], which you should do if
its pointing toward a form e.g.

TotalSum = DSum("Credit", "TestCreditDebits", "[Sales Date] < #" &
Forms!YourFormName![SalesDate] & "# ")

If you want to do this with a recordset rather than with DSum, then the code
would look like:

Set rs = CurrentDb.OpenRecordset("SELECT Sum([TestCreditDebits].Credit) AS _
TotalCredits FROM TestCreditDebits WHERE _
[TestCreditDebits].[Sales Date] < #" _
& Forms!YourFormName![SalesDate] & "#")
If rs.EOF = False Then
Me!TotalCreditsControl = rs.TotalCredits
Else
Me!TotalCreditsControl = "No Credits"
End if
rs.Close

HTH

Signature

Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

> Need help with formatting the DSUM date criteria. The following code is in
> the Report Footer.
[quoted text clipped - 12 lines]
> [SalesDate] & "# ")
> End With

Reply to this Message