Date comparisons in Microsoft Access where date format varies

I have a database at a client where different users have different date formats in the Regional Settings for Windows. Some have year/month/day, some have month/day/year, and some have day/month/year.

I have a form where the user can enter a range of dates to filter the form. There are text boxes called txtFromDate and txtToDate where the user selects the date range.

I thought that using the # to indicate dates would deal with the Regional Settings issue, but it turns out I'm wrong.

I'm using this code:

strRecordSource = "SELECT * FROM tblInvoices WHERE InvoiceDate >= #" & txtDateFrom & "# And InvoiceDate <= #" & txtDateTo & "#"

But it doesn't identify dates properly on systems where the Regional Settings have dates as day/month/year.

As a test, I set my settings to day/month/year and went into immediate mode and typed this:

? format(#31/12/14#,"yyyy-mm-dd")

It resulted in:


So clearly I was wrong in thinking that the # would make Access handle Regional Settings for dates properly.

I don't want to enforce a date format for the entry field because that kills the Access date picker.

Is there a good way around this problem? Or am I stuck enforcing date formats and then using some other date-picker?

Thanks in advance!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrmcanada2Author Commented:
I found a solution ... I'm now using DateValue() instead of the #'s.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.