strange date format problem

[JohnBeton]

Hi everyone,

I have a problem with date formats:
I have a date-field in a table with a short-date format (according to our regional settings (belgium) it is dd/mm/yyyy).
I use vba to insert data in the table, for the date-field i use the following code:
#" & Format(Date, "dd/mm/yyyy") & "#

what happens:
if the day-part is 12 or smaller he switches month and day, even thought the format is specified.
if the day-part is 12 or greater, there are no problems.

ex:
12/03/2005 (12 March) becomes 03/12/2005 (12 December) after insert.
13/03/2005 (13 March) stays 13/03/2005 (13 March)

I use like 10 different access databases (office xp) some have this problem, some don't. (it's NOT a date-setting problem in windows)

 

[PHV]

Simply use non ambigue date format:
#" & Format(Date, "yyyy-mm-dd") & "#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JohnBeton]

I find it a bit strange, but it actually works !!

thanks a lot !!

 

[VBslammer]

Looking at your question, I'm thinking since an Access Date/Time field can store the time as well as the date, the 12 might have something to do with the time (AM/PM) - maybe in relation to using the Now() function instead of the Date() function? Not sure how...

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ProgramError]

or try

#"& format(date,"shortdate")&"#

IM