UK date headache

[jalldridge]

Hi guys

I'm having issues with getting UK dates to show in access.

I created a table and then undertook an insert with the following:

docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"

Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be

If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.

I tried to use the following in the field section:

result: format(dates, "dd/mm/yyyy")

and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)

Any ideas how to overcome?

 

[DaveJHPT]

Date constants in SQL are always US format.

You'll have to use

docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#06/01/2005#);"

Dave

 

[jalldridge]

Thanks Dave,

Doing that seemed to solve my issue.

Something to remember in future....

 

[DaveJHPT]

Not nice though, is it? At least when you do it, it's all hidden away from the users in code & you can comment it to remind yourself!