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:
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!