DSum dates (Acces97SR2)

Topic

New Reply

Advice please on running Dsum with date values.
In a query I want to sum number of docs whose date is less than and including current record.
Table comprises three fields:
field1: Sys_number – three figure system number such as 100;101;120;345 etcetera
field2:Number_of_docs – number of documents in this system
field3:Expected_Date – short date format field
Query comprises:
field1:Sys_Number
field2:Number_Of_Docs
field3:Expected_Date (sort ascending)
field4:Runsum:DSum(“NumberOfDocs”,”tblSafetyPriorityDocs”,”[Expected_Date]<=" & [Expected_Date] & "")

Each time the query tries to run I get dialog box with:
Syntax error (missing operator) in query expression '[Expected_Date]<='.

I'm sure we had success previously by using DSum("NumberOfDocs","tblSafetyPriorityDocs","[Expected_Date]<=#" & [Expected_Date] & "#")
to enclose the date field but I searched through the help files and MS suggest # is not necessary if the fields are already defined as date format. (All fields are defined according to their data as the norm).

I have also tried forcing the format of the field using:
DSum("NumberOfDocs","tblSafetyPriorityDocs","[Expected_Date]<=" & Format([expected_date],"dd/mm/yy"))

Any suggestions anyone?
TIA
Alan
Cheshire
UK

Reply | Quote

Replies

You should always use US date format in SQL. Try the following:

DSum(“Number_of_Docs”,”tblSafetyPriorityDocs”,”Expected_Date<=#" & Format([Expected_Date],"mm/dd/yyyy") & "#")

Reply | Quote

Thanks Hans. Just tried it and the results are as expected. Excellent.

Reply | Quote