Report ignores Date Filter

[SarahG]

HI,
I've built a form which gathers user criteria and from this builds a WHERE clause which is passed to the OpenReport method of DoCmd:

DoCmd.OpenReport "rpt001", acViewPreview, , sReportCriteria

where sReportCriteria denotes the string containing the user criteria.

This works fine, except when I pass Date values. The data is definitely there, and if I type the date criteria directly into a query, data is returned. I've ruled out Regional Settings as a factor too.
Any ideas?

 

[dhookom]

You haven't provide the code that details how you build the sReportCriteria value. Have you tried debugging the variable with either a BreakPoint or Debug.Print sReportCriteria?

You can also add a text box to your report to see the full where clause as used in the report:
=[Filter]


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

 

[SarahG]

Hi Duane,
I have since solved it - by forcing a format of the dates entered to DD MMM YYYY format, despite the Regional settings being correct and despite being able to write a query to the same effect using DD/MM/YYYY formatting.

The code incidentally was:
sDateCriteria = " [IssueDate] BETWEEN #" & Me.txtBeginDate & "# AND #" & Me.txtEndDate & "# "

followed later in code by sReportCriteria=sDateCriteria

and is now:
sDateCriteria = " [IssueDate] BETWEEN #" & Format(Me.txtBeginDate, "dd mmm yyyy") & "# AND #" & Format(Me.txtEndDate, "dd mmm yyyy") & "# "

The variables, on tracing, showed the correct values. Strange. Thanks for your suggestions, I didn't know about the =[Filter] trick.