Correct date format

[John]

Hi

I have a query as a string and the where part uses a date in criteria as
below;

" WHERE (((MyTable.Date)>= #" & Format(Now(), "mm/dd/yyyy") & "# "

How can I ensure that date value is correctly used independent of the date
locale? I use UK date locale (dd/mm/yyyy) in windows regional settings.

Thanks

Regards

 

[John Spencer]

Try using the unambiguous format of yyyy-mm-dd
" WHERE (((MyTable.Date)>= #" & Format(Now(), "yyyy-mm-dd") & "# "

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Sheridan]

I don't see any problem here. Date/time data is stored as a 64 bit floating
point number, so the regional short date format in use is immaterial in this
context. You've correctly formatted the current date in US short date format
when building the literal date in the string; this will be interpreted
correctly as the current date regardless of the regional format set in
Windows Control Panel. The underlying values in the column in the table are
the same regardless of the regional format in use, so the expression in the
WHERE clause will evaluate correctly on any system.

As John has pointed out you can also use an internationally unambiguous
format (e.g. one corresponding to the ISO standard notation of YYYY-MM-DD),
but the format you've used will work equally well in Access.

BTW to ensure that date/time parameters in a query are interpreted correctly
be sure to declare them as such, e.g.

PARAMETERS Forms!MyForm!txtDate DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= Forms!MyForm!txtDate;

If you don't declare them there is the chance that the parameter value if in
a format such as 09/03/2008 could be interpreted as an arithmetical
expression rather than a date, in this case evaluating to 0.00149402390438247
which is the underlying value for 30 December 1899 00:02:09, not 9 March
2008. Note that you don't need to format the parameter value, which can be
entered in the local date format.

Ken Sheridan
Stafford, England