Access date parameter problem

[AHT Barry]

I have been struggling with this for days. I am using a form to retrieve a list
of dates and of store numbers. The user selects a date and store number from
each list and the form send the selections to another page. I have the date
formatted as #DateFormat(Visit_Date, "mm/dd/yyyy")#. This page is working
properly. The second page is where I am having problems. This page has two
cfsets, one for location number and another for the date. When I use only the
location number only, the database returns the correct results. When I use the
date parameter (both alone and with the location number parameter) the query
retrieves 0 rows. Yet if I type the exact same date in Access and run the query
it works. Below is the query using the ColdFusion debugging tool to see the
exact query being submitted (I have bolded the trouble area).

From the debugging report;
SELECT Locations.Location_ID, Locations.Location_Code, Visits.Visit_Date,
Visits.Visit_Time, PointRanges1.Group_Label, QuestionSet1.Question_MainText,
QuestionSet1.A_Answer_Points, Evaluations.Total_Points, Evaluations.Comments
FROM Locations INNER JOIN (((Visits INNER JOIN Evaluations ON Visits.Visit_ID
= Evaluations.Visit_ID) INNER JOIN QuestionSet1 ON Evaluations.Question_ID =
QuestionSet1.Question_ID) INNER JOIN PointRanges1 ON QuestionSet1.Group_ID =
PointRanges1.Group_ID) ON Locations.Location_ID = Visits.Location_ID
WHERE (((Locations.Location_Code)=22345) AND ((Visits.Visit_Date)=3/7/2005)
AND ((QuestionSet1.Group_ID)=1) AND ((Visits.Confirmation_Number)<>0) AND
((Visits.Customer_ID)=1) AND ((Visits.Survey_ID)=8))
ORDER BY Visits.Visit_Date;

Working Query in Access
SELECT Locations.Location_ID, Locations.Location_Code, Visits.Visit_Date,
Visits.Visit_Time, PointRanges1.Group_Label, QuestionSet1.Question_MainText,
QuestionSet1.A_Answer_Points, Evaluations.Total_Points, Evaluations.Comments
FROM Locations INNER JOIN (((Visits INNER JOIN Evaluations ON Visits.Visit_ID
= Evaluations.Visit_ID) INNER JOIN QuestionSet1 ON Evaluations.Question_ID =
QuestionSet1.Question_ID) INNER JOIN PointRanges1 ON QuestionSet1.Group_ID =
PointRanges1.Group_ID) ON Locations.Location_ID = Visits.Location_ID
WHERE (((Locations.Location_Code)=22345) AND
((Visits.Visit_Date)=["Visit_Date"]) AND ((QuestionSet1.Group_ID)=1) AND
((Visits.Confirmation_Number)<>0) AND ((Visits.Customer_ID)=1) AND
((Visits.Survey_ID)=8))
ORDER BY Visits.Visit_Date;

As mentioned before, when I run the working query in Access and type in
"3/7/2005" the correct data is return.

Any help would be truly appreciated.

Barry

[JMGibson3]

Try the CreateODBCDate function which will pass a DateTime object through your
ODBC pipe. Another alternative that might work (can't remember if it's only
Insert/Update vs. Where clause) is to surround it with single quotes even
though it's a Date. Access has been know to "forgive" this minor violation. I
think it might be for OLE rather than ODBC pipes though pipes. Also if your
OLE, CreateODBCDate will NOT work instead you must use
####DateFormat(Visit_Date, "mm/dd/yyyy")###, extra #'s to wind up with a single
# surrounding the date in the Where clause which is how Access really likes to
see date constants.

[AHT Barry]

TheCreateODBCDate worked! Thank you very much. All I did was change the SQL
state to read ((Visits.Visit_Date)=#CreateODBCDate(The_Date)#) and the page
worked correctly.

Thank you very much for all of your help.

Barry