Full Version: Date Format Switching From UK To Usa
UtterAccess Discussion Forums > Microsoft® Access > Access Date + Time
MSAccessFan
In my computer's Control Panel > Regionand Language settings, the date format is set to English (United Kingdom) as usual.

In Access, I have a table with a date field that stores dates & works just as I'd expect, except when I add a new field to this table using code. In this case, the date switches to USA format (mm/dd/yyyy). For example, I enter 01/02/2003 (1st February 2003), and the date is displayed in the table as 02/01/2003, which looks like 2nd January. I've checked everything I can think of. When I insert a date directly into the table, it stays as I'd expect, so I suspect this is being caused by the code that I'm using to write the new record:

CODE
CurrentDb.Execute "INSERT INTO tblLog (fldID, fldToBeActionedByDate) VALUES (" & Me.txtNewID & ", #" & txtNewToBeActionedByDate & "#)"

Could anyone shed light on this for me please?
TheForce
AIUI, the default format in VBA for dates is mm/dd/yyyy. Try using the Format function in your code to change it to dd/mm/yyyy.
MSAccessFan
Thanks for the suggestion.

The help article for Format says to use "Short Date" to "display a date using your system's short date format." My system's short date format is dd/mm/yyyy but it still returns 02/01/0003 when I enter 01/02/2003.

I tried Format(txtNewToBeActionedByDate, "dd mm yyyy") but that didn't help either.

Am I going wrong?
Gustav
Use the ISO format:

CurrentDb.Execute "INSERT INTO tblLog (fldID, fldToBeActionedByDate) VALUES (" & Me.txtNewID & ", #" & Format(txtNewToBeActionedByDate, "yyyy\/mm\/dd") & "#)"

/gustav
cheekybuddha
Hi,

Do not worry about how Access stores your date in the table. You/your users should avoid direct contact with the table anyway, and use a form to access the data.

If you make a form and bind the date field to a textbox then you *should* see your date in the format as defined by your regional settings.

Further issues will arise when you need to use dates in your SQL statements. These require dates to be passed either in US date format (#mm/dd/yyyy#), or in an unambiguous format like #yyyy/mm/dd#. You will need to use the Format() function to make the date string into the format that the SQL statement expects.

The trick is to distinguish between how dates are stored and how dates are displayed.

This is always confusing, so shout if you need further clarification.

hth,

d
MSAccessFan
That's fantastic, thanks, it works perfectly now.

I've done a bit of searching on the ISO date format to find out a bit more, as I'd never heard of it before. Very helpful to know this, thanks very much.
MSAccessFan
Also thanks to cheekybuddha, I didn't see your post until I'd sent my previous reply.

The thing that confused me was that I entered a date in dd/mm/yyyy format but it was then being converted by the SQL into mm/dd/yyyy format and being stored that way in the table, also later displaying like that on forms. I've changed the code to use the ISO format that you and Gustav mentioned, and it works fine now, thanks.
cheekybuddha
Great, glad we could help! thumbup.gif

d
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.