Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filtering between 2 dates

Status
Not open for further replies.

balin

Technical User
Dec 6, 2002
89
AU
I am used to doing queries between 2 dates. Dates are usually stored in short date format from the Date() function

I have a new project that stores TransactionDate as Date/time type. Generated with Now() function to record the time as well as the date.

Now when I try to filter between 2 dates it doesn't filter properly (shows all records). I thought it was because I am picking up the date parameters from a form that has date controls formatted as short date.

I've tried converting to short date format in the query first but same result.

I think I'm missing something basic.
JON



 
If I am using full date/time, i normall use the full date format.


Between #12/01/2008 00:00:00# and #12/31/2008 23:59:59#

Remember is you use dd/mm/yyy as your normal date format, SQL requires you to switch them to US format mm/dd/yyy first. Who thought up that one!?

SeeThru
Synergy Connections Ltd - Telemarketing Services
 
What you're missing is that dates are not stored in any particular format. They are floating-point doubles with the date before the decimal place and the time of day after it. "Short Date" and "Long Date" are just descriptions of how that numeric value is displayed.

You can go with SeeThru's suggestion or you can truncate the date field in the BETWEEN clause.
Code:
CDate(Int([DateField])) BETWEEN #12/01/2008# AND #12/31/2008#
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top