Date Range - filter doesn't work 1

[klfduncan]

Morning,

I've searched through the related threads on this topic but still can't get my date range to work.

I wrote a macro to create the filter and that all works fine. However the filter (or more specifically the WHERE clause) doesn't get the data I know is there.

Previously I typed in:

stDateFilter = "[leadsInfo].[LastUpdate]>=#03/08/2004# AND [leadsInfo].[lastUpdate]<=#31/08/2004#"

Docmd.OpenReport "LeadsWon", acReportPreview, , stdatefilter

This doesn't work. So I tried:

stDateFilter = "[leadsInfo].[LastUpdate] Between #03/08/2004# AND #31/08/2004#"

Docmd.OpenReport "LeadsWon", acReportPreview, , stdatefilter

This doesn't work either. It seems rather odd, as when I've created a query which retrieves the correct data and looked at the WHERE clause in the SQL view, it is identical (although it translates it to mm/dd/yyyy).

Any help would be greatly appreciated

 

[klfduncan]

I should also add that I'm using Access 2002. I use the valueDate() method to convert from "September/3/2004" to a useable date, and it always returns it in British form:

"03/09/2004", which are the settings in my control panel. Do I have to use American format to make this work?

 

[dhookom]

You must use the American format of dates in queries and where conditions for opening forms and reports.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[klfduncan]

Cheers mate,

it's just a bit confusing when the dateValue function returns it in British.