Access should always treat dates with year first in ISO format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Different machines can be configured to use different languages. This is
particularly a problem as the Windows setup Wizard makes you have to change
the language/regional settings in no fewer than 4 places, and non-savvy users
do not realise that this is the case.

You therefore end up with one computer set to US an one to GB. This means a
date like 09/03/2006 can be misinterpreted, as the standard monotonic
interpretation (i.e. 9th March 2006) is reveresed in the US (i.e. DD/MM/YYYY
is the GB setting, but MM/DD/YYYY is the US setting).

To get around this on most (other) database systems one can enter the date
in the ISO format YYYY-MM-DD, e.g. 2006-03-09 always means the 9th March.
However in Access and SQL Server this is sometimes, seemingly irratically
interpreted as the 3rd September.

Since the behaviour is inconsistent, it is nigh on impossible to write code
with predictable results causing severe embarrassment with clients.

Also, occasionally, a time is displayed in a field where a date is entered
for apparently no good reason.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...6688&dg=microsoft.public.access.modulesdaovba
 
coolvid said:
Different machines can be configured to use different languages.
This is particularly a problem as the Windows setup Wizard makes you
have to change the language/regional settings in no fewer than 4
places, and non-savvy users do not realise that this is the case.

You therefore end up with one computer set to US an one to GB. This
means a date like 09/03/2006 can be misinterpreted, as the standard
monotonic interpretation (i.e. 9th March 2006) is reveresed in the US
(i.e. DD/MM/YYYY is the GB setting, but MM/DD/YYYY is the US setting).

To get around this on most (other) database systems one can enter the
date in the ISO format YYYY-MM-DD, e.g. 2006-03-09 always means the
9th March. However in Access and SQL Server this is sometimes,
seemingly irratically interpreted as the 3rd September.

Since the behaviour is inconsistent, it is nigh on impossible to
write code with predictable results causing severe embarrassment with
clients.
Click to expand...

I have never seen Access nor SQL Server interpret a date formatted as
0000-00-00 as anything but YYYY-MM-DD.

Can you reproduce a case where this is not true?
 
and non-savvy users do not realise that this is the case.
Click to expand...

It's not a problem for users: it's a problem for developers. There is
loads of useful information about handling dates in Access on the web.
There is no excuse for letting your users get away with entering dates
wrong.
To get around this on most (other) database systems one can enter the
date in the ISO format YYYY-MM-DD, e.g. 2006-03-09 always means the
9th March. However in Access and SQL Server this is sometimes,
seemingly irratically interpreted as the 3rd September.
Click to expand...

More information here: I have never seen this, and always found ISO-like
dates to be unambiguously interpreted. In fact it's even robust enough
to treat this:

? cdate("2006-30-04")

as an error. Try that with 30/04/2006 and 04/30/2006.
Since the behaviour is inconsistent, it is nigh on impossible to write
code with predictable results causing severe embarrassment with
clients.
Click to expand...

Well, it would be if true. Please supply a method to reproduce this
behaviour.
Also, occasionally, a time is displayed in a field where a date is
entered for apparently no good reason.
Click to expand...

I am suspecting you have a broken setup. A time is _always_ present, but
(a) if you only enter a date value, it is set to midnight, and (b) if
it's midnight the General format will not display it. You need to (i)
get a grip on your text box format properties, and (ii) get a grip on
how date-time values are entered into your records.

Hope that helps


Tim F
 
Back
Top