Date Format

[Guest]

Our date format is dd/mm/yyyy. when is use a query and i want to compare a
specific date with another one which should match, but i get wrong results.
after debugging I noticed that it flips one of the dates to the format
mm/dd/yyyy. e.g. when i want to compare "06/07/2006" (July 6,2006) with
another same date I got the other one as "07/06/2006", so the criteria will
not be met and i get a wrong result. how can i solve this proble?

 

[Douglas J Steele]

While your dates may appear to you as being in dd/mm/yyyy format,
internally, they're strictly a number (specifically, an eight-byte floating
number where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day).

In queries, Access wants dates in mm/dd/yyyy format, regardless of what your
short date format is set to. (Okay, this isn't 100% true: it'll accept any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, it's
going to assume mm/dd/yyyy format unless what it's given doesn't work in
that case. 13/07/2006 will be interpretted as 13 July, 2006 because there is
no 13th month. 12/07/2006 will be interpretted as 07 Dec, 2006.)

 

[Douglas J Steele]

Forgot to add that you might want to read Allen Browne's "International
Dates in Access" at http://allenbrowne.com/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Tim Ferguson]

Our date format is dd/mm/yyyy. when is use a query and i want to
compare a specific date with another one which should match, but i get
wrong results. after debugging I noticed that it flips one of the
dates to the format mm/dd/yyyy. e.g. when i want to compare
"06/07/2006" (July 6,2006) with another same date I got the other one
as "07/06/2006", so the criteria will not be met and i get a wrong
result. how can i solve this proble?

First of all, dates are stored internally as serial numbers, so that
comparing dates with other dates always works as planned. Vis

WHERE MyTable.SomeDate > Date()

or

WHERE MyTable.SomeDate = DateSerial(2006,5,9)

etc always work as planned.

The problem comes when date values are transported into and out of their
text representations. Jet itself will only read dates in a small number
of non-ambiguous formats. These include mm/dd/yyyy or yyyy-mm-dd;
dd/mm/yyyy is only parsed when dd is greater than 13, which to my mind is
a bug. Note also that the dates must be delimited with box characters vis
#2006-07-06#.

Access will parse dates according to local settings: if you type a date
into the query grid like 25/12/2005 and then switch to the SQL window,
you will see that behind-the-scenes it has been changed to #12/25/2005#.
Generally, if you don't try to mess about with this you'll get the
correct answer.

VBA also changes dates around according to local settings. This can be
really unsettling, as if you type in this

Const firstAvailableDate as Date = #2006-04-09#

it will immediately recast it to
Const firstAvailableDate as Date = #09/04/06#

which was really creepy and potentially dangerous around the turn of the
last century! At least you can make dates unambigous in the IDE like
this:

Cont lastAvailableDate as Date = "2006-06-30"

and that stays as it should. Pity the Visual Studio won't do the same!!

Anyway, the moral is this:

1) Remember Jet is regionally-UNaware: always use international or USA
dates explicitly formatted. Just not using text literals is even better
but not always possible.

2) Trust VBA and Access without thinking too hard about it, but remember
to test things carefully afterwards.

Hope that helps


Tim F

 

[Guest]

I used the quotes instead of ## and it solved my problem. thanks