Access 2000 date query problems

[PeteCan]

As part of the reporting side of the application I'm writing there is a need to show how many incidents occurred between certain dates, or during a calendar month.
I was on another site

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=59

which looked as if it would be really useful and solve my problems however when the user actually enters the start and end dates the resulting query seems to take no notice of the startdate field.
The query is

strSQL="Select ILNumber, ProblemDate, Severity, Service, Active FROM ILDatabase WHERE ProblemDate BETWEEN #" & StartDte & "# AND #" & EndDte &"#" & " ORDER BY ILNumber DESC"

when using the calendar month query the same seems to happen
strSQL = "SELECT ILNumber, ProblemDate, Severity, Service, Active FROM ILDatabase WHERE ProblemDate BETWEEN #" & Mthfirst & "# AND #" & Mthlast &"#"

Anybody tell me why this is?

 

[AccessGuru]

Try putting the format([your date],"mm/dd/yyyy&quot around all your dates cos it must be be converted to US format to query correctly. U can also use the CVDate command for string to date conversion.

 

[PeteCan]

Sorry being a bit stupid here but can you explain the mydate bit, do you mean it should look like

strSQL="Select ILNumber, ProblemDate, Severity, Service, Active FROM ILDatabase WHERE ProblemDate BETWEEN #" & ([StartDte],"mm/dd/yyyy&quot & "# AND #" & ([EndDte],"mm/dd/yyyy&quot &"#" & " ORDER BY ILNumber DESC"