Help with query using dates

[Ruckus50]

I am trying to select records based on a date range, this is the query, it
doesn't like the format of the ##form.StartDate## and ##form.EndDate:

<cfquery Name="Requests" Datasource="#AuditSoftDSN#">
SELECT *
FROM SoftwareRequests
WHERE SoftwareRequests.DateAdded >= ##form.StartDate## AND
SoftwareRequests.DateAdded <= ##form.EndDate##
ORDER BY SoftwareRequests.DateAdded;
</cfquery>

The database is MS Access. Any help is appreciated.

[paross1]

You don't need the # for the Access date type column from within ColdFusion.

Try

<cfquery Name="Requests" Datasource="#AuditSoftDSN#">
SELECT *
FROM SoftwareRequests
WHERE SoftwareRequests.DateAdded >= #form.StartDate# AND
SoftwareRequests.DateAdded <= #form.EndDate#
ORDER BY SoftwareRequests.DateAdded;
</cfquery>

--or--

<cfquery Name="Requests" Datasource="#AuditSoftDSN#">
SELECT *
FROM SoftwareRequests
WHERE SoftwareRequests.DateAdded >= #CreateODBCDateTime(form.StartDate)# AND
SoftwareRequests.DateAdded <= #CreateODBCDateTime(form.EndDate)#
ORDER BY SoftwareRequests.DateAdded;
</cfquery>

Phil

[Ruckus50]

#CreateODBCDateTime(form.StartDate)#, it worked using this format.

#form.StartDate#, did not work.


Thanks for your help.

[paross1]

Welcome. I haven't used Access since using it with ColdFusion 4.5 so I wasn't sure which one might work the best.

[ColdSteel2]

I noticed that you used
where dbdate>=#createodbcdate(form.startdate)#
and dbdate<=#createodbcdate(form.Enddate)# .
This has one problem:
If startdate is 31/1/06 and Enddate is 3/31/06, you wont get data from
3/31/06 if the datab ase field stores that data a date time (there will be a
fraction greater than todays date). It does not mater if you use
createodbcdate() or CreateODBCDateTime() if you r user is typing in 3/31/06.

A better solution is to have your where cluse look like
where dbdate>=#createodbcdate(form.startdate)#
and dbdate<#createodbcdate(form.Enddate+1)# .
This will also not mess up the databases use of indexes.