DSUM Date Criteria

[Guest]

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

 

[Jeff Boyce]

?[Sales Date] < [Sales Date]?

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

 

[SA]

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