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:


Could anyone shed light on this for me please?

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.

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?

Use the ISO format:

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

/gustav

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

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.

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.

Great, glad we could help!

d