Dates don’t work within WHERE clauses

  • Thread starter José António Silva
  • Start date

José António Silva

If you work with ODBC linked tables to SQL Server, you should read this!

Certainly, you have to deal with date fields within WHERE clauses and also
have, for many other reasons, a VBA defined reference to ADOX, i.e.,
“Microsoft ADO Ext. 6.0 for DDL and Securityâ€.

The following seven easy steps should produce the SERIOUS error I’m trying
to describe.

1) Choose any SQL database that contains a table with a DATE field (very

2) Create a new access database (2007, for instance), then create a linked
table to the chose SQL table with a DATE field. (you may use Windows ODBC
manager or use a DNS less connection).

3) Now, create an Access Form based on the linked table. You may use the

4) In the form “on open†even type the following code: Me.RecordSource =
"SELECT YourSqlTable.* FROM YourSqlTable WHERE YourSqlTable.YourDateField <

5) Add a VBA reference to “Microsoft ADO Ext. 6.0 for DDL and Securityâ€.
Don’t worry about priority. We will not use ADOX here.

6) You have now to setup your Access Database to open the form you have just
created on startup. Test it! See if the form opens properly on startup and
then, compile the accdb creating an accde file.

7) Finally, move the accde to a machine where only Access 2007 run-time is
available (NOT the full Access version). Run the accde to open the form and …

It raises the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string. (#241)

If you simple remove the VBA reference to “Microsoft ADO Ext. 6.0 for DDL
and Security†the error disappears.

My workaround for this is to replace … < #1/1/2009# with < CDate (
“â€2009-01-01â€â€ ) (remember to use double Ҡinside the SQL string).

However, I frequently wonder if/when this workaround might fail, what will I

This comes, at least, from Access 2000. I’m looking forward seeing this

José António Silva

Tom van Stiphout

On Wed, 2 Sep 2009 17:16:01 -0700, José António Silva

Since you're talking SQL Server, have you tried its date string
terminators (single-quotes):
WHERE YourSqlTable.YourDateField < '1/1/2009'

Microsoft Access MVP

José António Silva

Remember that, with an ODBC connection, you are working with Access Database
Engine - or Jet - between you and SQL. So, you have to write ADE/JET SQL. And
this means dates within ##. (although I’m using CDate as a workaround).

However, I tried < '1/1/2009' after reading your answer and it didn’t work.

José António Silva

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question