Very very strange problem

  • Thread starter Thread starter Bilbo
  • Start date Start date
B

Bilbo

Hi
I'm using Access 2003 as a front end to SQL server 2000. Yesterday many of
the dates I save (but not all) started being stored in American (rather than
English) date format. I've wracked my brains to try to work out what
happened and why but can't. I don't think any software changed. Most of my
systems have Windows XP pro and 1 has Vista but it's the same on them all.
Does anyone have any ideas or could point me inthe right direction? Thanks
and regards
 
Are you using SQL against linked tables, or are you using pass-through
queries?

If SQL against linked tables, Access will always try to use mm/dd/yyyy
format even if your regional settings are set to something else. It's only
when the date cannot be interpretted as mm/dd/yyyy (i.e.: days of 13 or
greater) that Access will treat the dates as dd/mm/yyyy. You're best off use
yyyy-mm-dd so that there's no confusion.

If you're using pass-through queries, how the date is interpretted is
controlled by a setting in SQL Server.
 
Bilbo said:
Hi
I'm using Access 2003 as a front end to SQL server 2000. Yesterday
many of the dates I save (but not all) started being stored in
American (rather than English) date format. I've wracked my brains to
try to work out what happened and why but can't. I don't think any
software changed. Most of my systems have Windows XP pro and 1 has
Vista but it's the same on them all. Does anyone have any ideas or
could point me inthe right direction? Thanks and regards
Click to expand...

Dates are never stored in a particular format. They are only displayed that
way.

If what you mean is that you are entering dates into forms in one format and
they are being saved in another then your Windows Regional settings might
have changed. This would affect any form control using the "named" formats
(long, medium, short) and any control with no format setting at all. I
always use explicit format strings like "mm-dd-yyyy" as this removes any
dependence on the local regional settings.

If you are using date literal strings in queries and such to store dates
then those always have to be in either US format, ISO format (yyyy-mm-dd),
or a completely non-ambiguous format that uses alpha characters. These do
not adhere to your Windows regional settings and they never have.

If you are using CDate() to convert strings to dates then that does use your
Windows regional settings as long as doing so doesn't produce an invalid
date. Then it will try other combinations until it results in a proper
date.
 
Back
Top