unexpected date format

[grenee]

Good night All.

I am collecting a date on a form in one format like so: 01/03/2021. This is intended to be 1st March 2021. This information is passed to a query, but in the query is is presented this way: #03/01/2021# . And this represents a date of 3rd January 2021.

This situation is baffling.

Has anyone ever experience this outcome? I would be grateful for any assistance.

 

[June7]

Review http://allenbrowne.com/ser-36.html

 

[arnelgp]

You need to Format() your date as mm/dd/yyyy (en-us date format) in your query.

 

[Gasman]

Good night All.

I am collecting a date on a form in one format like so: 01/03/2021. This is intended to be 1st March 2021. This information is passed to a query, but in the query is is presented this way: #03/01/2021# . And this represents a date of 3rd January 2021.

This situation is baffling.

Has anyone ever experience this outcome? I would be grateful for any assistance.

I am surprised it has taken you this long to discover this?

I have used this constant in the past when I had to build SQL strings (which you might have to at some time)

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

then use that as the Format string.

 

[Minty]

Please be aware that the query designer grid, will display dates in your Windows locale format.

However, if you switch to SQL view you will see that it changes the format to the default "US" format.
The easiest way to deal with this is to always use "yyyy-mm-dd" format in any SQL queries that you build in VBA, it is unambiguous and understood by both Access and SQL server if you ever migrate. The only difference being that Access uses # # as a date delimiter.

When you are debugging you know exactly what values you are looking at without having to allow for the switch in formats forced on you by the Access gnomes in the background.