Date stamping records (1 Viewer)

[Peter Bellamy]

I have a VBA routine which is using SQL to update a field in some records to indicate they have been actioned.
A debug.print shows that the SQL string has the date part in european format yet the date that get placed in the field is in US format !

My PC is set for UK format so I guess it is something Access is doing to flip it!

How can I keep it in UK format ??

Cheers

 

[DCrake]

A sure fire way is to save it as medium date

 

[Peter Bellamy]

Thanks.
I tried it and it too got converted to US format

Same as before everything appears UK except the end result in the table.

Cheers.

 

[HiArt]

use Format(yourdate, "dd-mm-yyyy")?

 

[Peter Bellamy]

Same thing, the month and day are reversed !!!!!

 

[Peter Bellamy]

OK I have found the explanation and solution.
SQL expects dates to be in US format, it then converts them using regional settings before putting them into to a table.

This knowledge base article explains:
http://support.microsoft.com/default.aspx/kb/210069/

I was using Now() to datestamp with so I just had to change it to US format before using it in the SQL statement.

Cheers