Date Formats

[Simon Harris]

Hi All,

I am having a problem with dates getting mixed up. Heres my setup:

- Windows XP Pro SP2, time zone correct
- Office XP Pro
- Column in table - Data Type: Date; Format: Short Date
- Combo Box on form: Format: Short Date
- When the user clicks the combo, a Calendar control appears, they pick the
date which then ends up as the value of the combo (Chose a combo as people
naturally click them)

Problem is this...

- If I choose 15/07/2006 (dd/mm/yyyy) all is fine.
- If I choose an ambiguous date, say 01/07/2006 (dd/mm/yyyy), the form is
AOK, but the value in the table is wrong, showing 07/01/2006 (mm/dd/yyyy)

Subsequent calculations based on the data are wrong, so I'm pretty sure the
date is being stored in the wrong format...But why?

Any help will be much appreciated...Itve spent over 12 hours on this
problem!

Thanks!
Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Guest]

Hi Simon - Have you checked the date formats (Long & Short) in Control Panel
Language & Regional Options? - not sure this is the problem but with checking?

 

[John Vinson]

- If I choose an ambiguous date, say 01/07/2006 (dd/mm/yyyy), the form is
AOK, but the value in the table is wrong, showing 07/01/2006 (mm/dd/yyyy)

Subsequent calculations based on the data are wrong, so I'm pretty sure the
date is being stored in the wrong format...But why?

The date is not "stored" in ANY format: it's stored as a double float
count of days and fractions of a day (times) since midnight, December
30, 1899.

Any time you use a date literal, it will be interpreted (if possible)
as an American-style mm/dd/yyyy format date. If your first two digits
are over 12, it tries dd/mm/yyyy as an alternative.

Try using an unambiguous format such as 07-Jan-2006 or ISO format
2006.01.07.

John W. Vinson[MVP]

 

[Joan Wild]

Access will interpret your entry using the setting in Regional Settings in
Control Panel for the computer.

For more information and how to deal with it see
International Dates in Access
at:
http://allenbrowne.com/tips.html