Problem querying Access DB for records within a certaindate range.

[liquidorb]

Hi Everyone,

I'm having a problem pulling records out of an Access DB that are between a
certain date range. The records are located in a Date/Time field in my DB and
the formatting for the field is set to General Date. I have a form where the
user will enter in a FROM and TO daterange (ex. FROM: 12/1/2005 TO:
12/31/2005) and then I want to pull all the records within those dates. The
dates/times in the DB are formatted like this: 12/17/2005 6:22:00 PM. My
query looks something like this:

<cfquery datasource="MyDatasource" name="MyQuery">
SELECT order_id, order_date, f_name, l_name
FROM orders
WHERE order_date BETWEEN #form.from_date# AND #form.to_date#
</cfquery>

The problem is in the WHERE clause. I've never had a problem inserting
dates/times into the DB, but I've actually never had a need to pull records by
them until now. I know that dates/times are pretty sensitive as far as the
type that is used, and I just don't know what syntax I should use in the WHERE
clause.

Any ideas would be much appreciated!

Thanks,

ORB

[The ScareCrow]

Try this

WHERE order_date BETWEEN #CreateODBCDate(form.from_date)# AND #CreateODBCDate(form.to_date)#

Ken

[liquidorb]

ScareCrow...you ROCK!

Thanks a million, it worked perfectly right off the bat! I'd been struggling with that for hours. THANKS!

[Dan Bracuk]

I do a lot of pages where the query is based on a date range.

Before I even try my query, I always ensure that both form fields are valid
dates, and they are in the proper order, and they are not too far apart, and
that there is data in that range.

[The ScareCrow]

Also to expand on Dan's responce.
As the date fields also have time values
I would also suggest manually checking the results, to ensure that the correct
results are being returned.
Sometimes because of the times, there might be some records missing from the
ending date.

Ken

[Dan Bracuk]

Originally posted by: The ScareCrow
Also to expand on Dan's responce.
As the date fields also have time values
I would also suggest manually checking the results, to ensure that the correct
results are being returned.
Sometimes because of the times, there might be some records missing from the
ending date.

Ken

Whether or not the date includes the time depends on the database. I work
with both.

If they do, something like this will work

where #yourdate# >= #startdate# and #yourdate# < dateadd(day, 1, #enddate#)

As always, the syntax depends on the database.