SQL Syntax Date format English v American (1 Viewer)

[andrewboast]

I am trying to query a set of records on a date field via code using memory variables as below:

dtFrom = Format(dtFrom, "dd/mm/yy")
dtTo = Format(dtTo , "dd/mm/yy") '

I have also tried using CDate in both cases the set of records return a selection that is equivalent to the dates being American format i.e. (mm/dd/yy) instead of the British format I have set. When I use the debug to print the sql string it shows the dates in the British format (dd/mm/yy). The regional settings and language setting on the PC are all British.

To confirm my theory if I enter the dates directly (American Format) in the sql string rather than using variables then I get the expect set of records.

To clarify when I use variables to restrict the records between 01/09/2016 and 04/09/2016 it returns records between 09/01/2016 and 09/04/2016.

However if enter the dates directly using the American setting #09/01/2016# and #09/04/2016# I get the expected set records over the 4 days.

Any assistance would be very welcome.

Andrew

 

[jdraw]

Here's a response from a similar post

Re: Date issues in different regions
1. The issue is not different date formats as such, but that SQL, when fed a string, needs the date string representation to be in the US format, no matter what the local date format is.

2. You may be surprised to know that you are not the first one with Access date issues.

Here is a link: http://allenbrowne.com/ser-36.html

Also a number of posts concerning this are on AWF.
Google is a good search tool.

 

[andrewboast]

Thanks jdraw, cannot believe I have not come across this issue before. The link cleared the issue for me.

 

[jdraw]

You are welcome. I have seen the issue many times. I have my Regional settings ith the date as DD-MMM-YY.