Parameter query for date field that has different formats

[Donna Wiley]

I have a date field that could be formatted m/d/yyyy, mm/d/yyyy, m/dd/yyyy, or mm/dd/yyyy. I need to be able to enter a start date and an end date in a parameter query, i.e. Between (date) and (date).

I've tried various scenarios for the parameter query and can't seem to find one that works. Do I need to format the field to always be the same date format, i.e. mm/dd/yyyy? I haven't tried this one yet.

Can someone help me?

Thank you!

[Stewart Ross]

Hi. Behind the scenes Access uses the ANSI standard format for dates when performing comparisons - which is indeed mm/dd/yyyy. If comparing date values as literal strings you need to enclose those values in pound signs to tell Access they are dates, like this:

BETWEEN #12/01/2010# AND #01/31/2011#

We have an Insights article which explains all this in detail, linked here for you: Literal DateTimes and their Delimiters

-Stewart