Date issue in Access 2007 SQL

[MTate]

I am investigating an SQL issue that exists in Access 2007 that doesn't exist
in Access 2003. I did not write the SQL code. I am assisting in resolving
the issue. Both versions of Access are running on a Citrix server.

Here is the problem:

In Access 2007 SQL, the following statement:
AND NESWHSLDT BETWEEN '02/14/2010' AND '02/20/2010' will convert to the
following when the date is changed to another date. For example when the 14
in 02/14/2010 is changed to 15, this is what the SQL changes to:
AND NESWHSLDT BETWEEN '02/015/2010' AND '02/20/2010'. An extra zero is
inserted into the SQL.

This does not occur with the same SQL running in Access 2003. We have
verified that all date formats are the same. Any help would be appreciated.

 

[Allen Browne]

Use # as the delimiter around the literal date values in your SQL statement,
not the single-quote character.

 

[MTate]

Thank You
--
Thanks

Use # as the delimiter around the literal date values in your SQL statement,
not the single-quote character.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.




.

 

[MTate]

This was tested today and it did not work. For some reason in the SQL
(Access 2007) once the statement is saved it inserts a ' in the SQL. So now
it reads BETWEEN ' #02/14/2010# and #02/14/2010#

Any ideas? This does not occur in Access 2003.

 

[John W. Vinson]

This was tested today and it did not work. For some reason in the SQL
(Access 2007) once the statement is saved it inserts a ' in the SQL. So now
it reads BETWEEN ' #02/14/2010# and #02/14/2010#

Any ideas? This does not occur in Access 2003.

Have you perchance defined this query as a PassThrough Query (to SQL/Server,
which does use ' as a date delimiter)?