Date/Time handling

[TOPSie]

I know this is one of the most confusing things in Access/vba, but can anyone explain this particular nuance



I have a Table with a field called PaymentDate defined as Date/Time.

In code I have a variable Dim wDate as Date.

I get wDate from the screen - wDate = Me.txtDate

So as I am in England I am using the dd/mm/yyyy format

Now I store it in the table
DoCmd.RunSQL "UPDATE Table SET Table.PaymentDate =#" & wDate & "#

If you now look at the table (looking at the entries made in December, you see that if the day number is > 12 then the date appears correctly (English form) but if the day is 12 or less the date gets converted to American

So 29/12/2022 appears in the table as 29/12/2022
but 6/12/2022 is rendered as 12/06/2022

So confusing

[Micron]

Perhaps this will help you understand what's going on
http://allenbrowne.com/ser-36.html

[Welshgasman]

Use USA format mm/dd/yyyy or yyyy-mm-dd every time.

I used this in some of my DBs after finding it on the net

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

[Minty]

I amended the Allen Browne code to use yyyy-mm-dd , it removes any ambiguity when you are debugging, and the same format (albeit with a different escape character) works in SQL Server.

[Welshgasman]

I know this is one of the most confusing things in Access/vba, but can anyone explain this particular nuance

I have a Table with a field called PaymentDate defined as Date/Time.

In code I have a variable Dim wDate as Date.

I get wDate from the screen - wDate = Me.txtDate

So as I am in England I am using the dd/mm/yyyy format

Now I store it in the table
DoCmd.RunSQL "UPDATE Table SET Table.PaymentDate =#" & wDate & "#

If you now look at the table (looking at the entries made in December, you see that if the day number is > 12 then the date appears correctly (English form) but if the day is 12 or less the date gets converted to American

So 29/12/2022 appears in the table as 29/12/2022
but 6/12/2022 is rendered as 12/06/2022

So confusing

Not really. The first is unambiguous, there are not 29 months, so has to be ddmmyyyy.
Second is ambiguous, is it dd/mm or mm/dd????
As Access expects/demands mmddyyyy ( MS is a USA company after all ) that is what it chooses to go for.

Up to you to say? and say which correctly.

Once you understand that, it is easy.