Resulting Date Format In Datasheet Is Inconsistent

[NonAccessGuru]

I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.

http://office.microsoft.com/en-us/templates/TC010175341033.aspx?CategoryID=CT101426031033

While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different<< Date/Time formats in the *same* table.

Below is the date range I've entered from January 2007 to May 2007 and below has been copied and pasted directly from the datasheet. Trying to do a date sort in any fashion is out of the question until I resolve this.

I am consistent in my date input... April 6, 2007 is entered as 4/6/07, January 18, 2007 is entered as 1/18/07, February 21, 2007 is entered as 2/21/07 and so on.

However, some dates are showing as mm/dd/yyyy format while others are showing as dd/mm/yyyy format.

Note: if I choose a general date or long date format Access reads these as July, September and October dates in some cases.

Here is January (as copied and pasted from the datasheet)

01/03/2007 (these first 7 show a mm/dd/yyyy format)
01/03/2007
01/02/2007
01/10/2007
01/09/2007
01/05/2007
01/11/2007


18/01/2007 (these last 6 show a dd/mm/yyyy format)
24/01/2007
27/01/2007
27/01/2007
02/01/2007
31/01/2007

Here is February's

05/02/2007
02/07/2007
26/02/2007
02/09/2007
21/02/2007
28/02/2007
15/02/2007
04/02/2007

And March's... (the only month that appears correct...)

14/03/2007
17/03/2007
21/03/2007
14/03/2007
19/03/2007
22/03/2007
14/03/2007
15/03/2007

April's....

04/11/2007
04/05/2007
04/10/2007
04/10/2007
04/11/2007
04/11/2007
04/11/2007
04/11/2007
13/04/2007
04/12/2007
04/12/2007
13/04/2007
13/04/2007
13/04/2007
04/04/2007
20/04/2007
04/06/2007
23/04/2007
24/04/2007
17/04/2007
17/04/2007
17/04/2007
27/04/2007
04/10/2007
04/10/2007
13/04/2007
24/04/2007

May's...

05/06/2007
05/01/2007
05/03/2007
05/08/2007
05/09/2007
05/09/2007
05/10/2007
05/11/2007
14/05/2007
14/05/2007
14/05/2007
15/05/2007
15/05/2007
16/05/2007
16/05/2007
17/05/2007
25/05/2007

Any help would sure be appreciated!

Thanks in advance.

Rod

 

[RuralGuy]

Here's some links you will find useful:
Storing, Calculating, and Comparing Date/Time Data
How to Format Dates as U.S. Dates Regardless of Regional Settings
Return Dates in US #mm/dd/yyyy# format
What do you have your Regional Language set to? English (United States)?

 

[NonAccessGuru]

Thanks for the reply, RuralGuy. Yes, I have the regional language set to US, English. I'm located in the Northwest of the U.S. I'll check out your links. In the format field of the Date field in the table I even tried ([mm/dd/yyyy]) instead of choosing one of the pre-selectable options (short date, long date, etc.) but it didn't change anything. I'm sure it's an easy fix but I'm just not aware of it.

Thanks again!

 

[boblarson]

The quick fix (not the explanation of how it happened - all I can say is "Microsoft") is to use a make-table query to create a new table with the exact same fields but to set the date field to a format in the query (i.e. MyDatefield:Format([OldFieldName],"mm/dd/yyyy"), and do not include the actual date field in the new fields and then delete the old table when done, rename the new table to the old name and rename the date field.

Just a suggestion on how to fix it.

 

[gemma-the-husky]

i'm in UK

trouble with date formats (when used in sql certainly)

if it can ONLY be a UK date it gets treated properlyeg

24/11/06 - can only be 24 november

if its legal in UK and US, it gets treated as US

eg 12/7/06 is 7th December (US), instead of 12th July (UK)

therefore you HAVE to put

format(date, "long date") to resolve it correctly in the sql

hope this helps