Error using date in Linked table query

Hello all

I am using Access 2000 as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.

He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.

I'm found some strange work-arounds but want to fix the problem the correct way. Below are two examples - the first one causes the error the second one is the work-around that returns the desired results.

1. Criteria >#1/1/2008#
2. Criteria >DateAdd("d",0,#1/1/2008#)

Any ideas?

I'm not sure that SQL server uses "#"

Have you tried:
Criteria > '01/01/2008'

Dykebert,
Thanks for the reply, but I don't think that's the fix.

Your right SQL does use dates in the '01/01/2008' format. Access forces the # - If you enter just 01/01/2008 it changes it for you. If you try '01/01/2008' or "01/01/2008" Access gives a Data type mismatch error. If I go to the SQL view you can change the criteria to just 01/01/2008, but then all results are returned as if the criteria is ignored.

Is this a known bug? Should I just live with the work-around?

I don't know about a bug, but it is certainly an inconsistency.

You could try forcing a pass through query or just use CDate rather than the DateAdd.

Using a parameter query also works. (ie. > [Enter Date])

Something is being lost in translation between Access and SQL Server. I'm not really an Access developer, but a SQL guy. I decided to use Access for this project to give the end users a little bit of control with Reports and Queries hoping that I wouldn't run into any of this funny stuff.

Thanks again.