Syntax error in date in query expression for Swiss Italian date format

  • Thread starter Thread starter Timothy M Hodgson
  • Start date Start date
T

Timothy M Hodgson

Using Access 2003, the query SELECT * FROM anyTable WHERE [datetime] =
#25.03.2004 10:16:43# returns the error: Syntax error in date in query
expression.

My OS is Windows 2003 Standard Server, my regional setting is for
Switzerland (Italian), the date format is dd.mm.yyyy.

If I change the query to use US (English) date formatting, e.g.
#3/25/2004 10:16:43#, the query runs without error. I've also tried
UK (English) formatting, #25/03/2004 10:16:43# without any problem.
The problem appears to be that Access does not recognize the
dot/period date format separator.

Is this a bug in the Jet Engine or do I need to do something different
with my query?

Tim Hodgson
 
It is a standard (rather than a bug) that date strings have
to be entered in the US format mm/dd/yy and enclosed in ##.
The reason that the UK example worked is because the
particular date was unambiguous. Had it been 3/1/2004, the
Jet engine would have assumed 1st March not 3rd Jan.

Hope This Helps
Gerald Stanley MCSD
 
Date literals in SQL statements must generally be in the US format,
regardless of your regional settings. However, Access (Jet) does understand
other unambiguous date literals in some other formats (which is way the UK
format is working for you, at least for days after the 12th of the month).

To avoid these kinds of problems, you might (in decreasing order of
preference):

1. Use a parameter query:

PARAMETERS [Your Date Parameter] DateTime;
SELECT *
FROM anyTable
WHERE [datetime] = [Your Date Parameter]

This way you don't have to worry about date formats, and your query does not
have to be compiled each time you execute it.

2. If you're building your SQL statement dynamically from a date supplied
as text, use the CDate function to convert it to a true Date, and use use
the Format function to explicitly format it to use the US format, as in:

Dim strSQL As String
Dim strYourDateLiteralAsText As String

strYourDateLiteralAsText = "25.03.2004 10:16:43"

strSQL = "SELECT * FROM anyTable WHERE [datetime] = #" &
Format(CDate(strYourDateLiteralAsText), "mm\/dd\/yyyy hh\:mm\:ss") & "#"

Note the "\" characters are needed to force Format to use literal "/" and
":" characters instead of the date and time separators specified by your
regional settings.
 
Back
Top