Select statement with date not working with ms access database

[tapuwa2002]

Hey guys

I was hoping you help, I have a ms access database on my webserver. I want to select all the records with a certain date. But it seems not to work.

<CFQUERY NAME="qStuff" DATASOURCE="MyDb" maxrows="20">
SELECT *  FROM Stuff;
WHERE (((Stuff.RegDate)=#5/1/2004#));
</CFQUERY>

When I do this in MSAccess sql view it works but doesn't work on cfm page. Please help:'( I have also tried shifting the date formats.

[cfwebdeveloper]

Why do you have semicolons? Have you tried removing them because I think that ends a sql query?

[tapuwa2002]

Yes I have removed them, but now it returns this decimal data type

(Stuff.RegDate)=11.4261363636)

[cfwebdeveloper]

I should of saw this from the beginning but you need three pound signs around your date ###5/1/2004###. See if that works.

[arrgh]

When I do this in MSAccess sql view it works but doesn't work on cfm page.

(Yeah, that's special "Access" syntax. It may work with some drivers, or it may not.) But the correct way to pass dates in cfquery is with cfqueryparam.

WHERE Stuff.RegDate = <cfqueryparam value="5/1/2004" cfsqltype="cf_sql_timestamp">

Please help I have also tried shifting the date formats.

That only changes the user display. It doesn't make any difference to your query.

[tapuwa2002]

Thanks