Access 2002 date format

[Maria]

My workplace just upgrade to Office XP from Office97 a
few weeks ago. I have a question about how Access2002
handling date format. It is hard to explain but I will
try...

No matter what format I set for the date field
(yyyy/mm/dd, or mm/dd/yyyy, etc...) and no input mask is
defined, then if I enter "02/06/2004"(Feb.6/04), the
system always interpretes it as "2004/06/02"(Jue.2/04),
Month and Day are switched. It only happens when both
Month and Day are less than 12, then the system
automatically assume it is dd/mm/yyyy. If the Day is
greater than 12, there is no problem and the system
understands data entered as mm/dd/yyyy is actually
mm/dd/yyyy.

Could anyone help me please? Thank you so much!

 

[Arvin Meyer]

More than likely your regional settings are forcing the date that way. On XP
go to the Control Panel, then choose Regional Options, then click the
Customize button, or change the country setting.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

My workplace just upgrade to Office XP from Office97 a
few weeks ago. I have a question about how Access2002
handling date format. It is hard to explain but I will
try...

No matter what format I set for the date field
(yyyy/mm/dd, or mm/dd/yyyy, etc...) and no input mask is
defined, then if I enter "02/06/2004"(Feb.6/04), the
system always interpretes it as "2004/06/02"(Jue.2/04),
Month and Day are switched. It only happens when both
Month and Day are less than 12, then the system
automatically assume it is dd/mm/yyyy. If the Day is
greater than 12, there is no problem and the system
understands data entered as mm/dd/yyyy is actually
mm/dd/yyyy.

Could anyone help me please? Thank you so much!

Check Windows... Control Panel... Regional and see what your
computer's default date setting might be. It sounds like it's the
European dd/mm convention.

Access has waffled back and forth in regard to how it complies with
the computer's regional setting.

 

[Pat Garard]

Hi Maria,

When you use dd/dm/yyyyy format as your 'standard' (which
I do), there are some things to watch for.

SQL is a STANDARD language that has little to do
with Microsoft. That standard REQUIRES dates to
be expressed in mm/dd/yyyy format.

CONSIDER designing a Query:

Access 'tries' to deal with mm/dd/yyyy and
dd/mm/yyyy formats (for me) but often gets
it wrong

In Query Design View, setting a criterion
'SomeDate =--/--/----'
gives rise to much confusion where the 'local' date
format is 'dd/mm/yyyy' BECAUSE IT NEEDS TO
BE SWITCHED AROUND FOR SQL - Access
itself will USUALLY get it wrong, especially if the date
entered CAN be interpreted as 'mm/dd/yyyy' -
ie when dd <=12.

Now SQL is the Essence of Access - even if the problem
is experienced where SQL is not normally visible - say
Search by Form.

This makes Access unusually 'sensitive' to regional settings.

My American Friends, who never have to work with this
regional format, often fail to understand our problem.

[FOR AUSSIES AND POMS:
|Firstly we need to understand that Dates are stored in
|a fixed internal format, any formatting we apply affects
|how they are displayed.
|
|The way to cope with this is for US to use d-mmm-yyyy
|format in all criteria and expressions (we just use it -
|Access will cope).
|
|We often encourage users to adopt the same format
|in response to INPUTBOX statements - rejecting
|formats containing '/', prompting with 'd-mmm-yyyy'
|AND inserting a default date in 'd-mmm-yyyy' format.
|
|On Forms, we may modify the label for Date Fields
|to include 'dd/mm/yyyyy' as a reminder for users -
|OR confine them to a 'd-mmm-yyyy' input mask.
|
|On Reports, of course, we do as we please, unless
|there are Date Parameters!!]
--
Regards,
Pat Garard
Australia

______________________________________