Preserve non-American date format 2

[a2ross]

I have had issues for years with Access converting Australian (British) date formats to the American format when using VBA.

My problem is the 5th February will be converted to 02/05/2007 but the 13th of February will be converted to 13/02/2007.

As there is no consistency in the conversion of the dates, I cannot consistently convert them back.

To the best of my knowledge, all date, region settings etc on the machine are set to my local area. I only encounter the error when I get to date comparisons or working in VBA.

Any suggestions?

 

[orna]

In vba use format(YourDate,"mm/dd/yy") for comparisons or sql queries.

 

[a2ross]

Thanks orna

My code extracts the date from one table in my desired format (I can see this by stepping through the code) and then writes it to another table. I have used your suggestion to tell it to write to the table in American format ie Format(!Date, "mm/dd/yyyy") and it now writes correctly to the table in my format ie "dd/mm/yyyy"

And I try to teach my students that computers are logical. Why do I bother. (-:

Thanks for the help.

 

[orna]

Computers are logical but we not always understand their logic.
Access keeps the date in the DB in the American format and displays it by the regional settings.

Access tries to convert the date 05/02/2007 and succeed but if it tries to convert 13/02/2007 to the American format it gives an erroneous date so Access leave it as is.

 

[hermanlaksko]

Orna is right on and deserves a star for that explanation!

Herman
Say no to macros

 

[PHV]

Access keeps the date in the DB in the American format
False !
Access keeps the date in the DB as a float number of days since 1899-12-30, the decimal part standing for the time.

But, the default format of a literal date value is US.

Bottom line, always use non ambigous date values:
Format(somedate, "yyyy-mm-dd")


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

 

[a2ross]

Thanks to all for the input. Hope it is helpful to others who have similar date issues.

I was always aware that the date was stored as a single number. It is just very frustrating when sometimes that number is decoded based on regional settings and sometimes on default Microsoft settings.

Thanks for the great tip of using the "yyyy,mm,dd" format so that no conversion issues should occur.