Trying to select data range for date/time column

[mscertified]

I've been struggling with this for hours: I have records with date/time
column (having date AND time). I need to select based on date only. No matter
what I do when trying to select for 8/1/2007 to 8/31/2007, I NEVER get any
records for 8/31/2007. And YES there are 8/31/2007 records there. Seems like
all 8/31/2007 records are being taken as 8/1/2007. What am I doing wrong?

Here is my latest failed attempt:
SELECT DateValue(RecordDate) AS RecDate, RecordType, HowMany AS Num
FROM tblAddress
WHERE DateValue(RecordDate) >= NZ([forms]![frmPrint]![StartDate],#1/1/2000#)
And RecordDate <= NZ([forms]![frmPrint]![EndDate],#12/31/2999#);

 

[mscertified]

OK, I finally solved it with this code:

SELECT DateValue(RecordDate) AS RecDate, RecordType, HowMany AS Num
FROM tblAddress
WHERE DateValue(RecordDate) >=
NZ(CDate([forms]![frmPrint]![StartDate]),#1/1/2000#)
And RecordDate <= NZ(CDate([forms]![frmPrint]![EndDate]),#12/31/2999#);

 

[John Spencer]

If the RecordDate has a time component then you need to strip off the time
component on the end date.


SELECT DateValue(RecordDate) AS RecDate
, RecordType
, HowMany AS Num
FROM tblAddress
WHERE DateValue(RecordDate) >= NZ([forms]![frmPrint]![StartDate],#1/1/2000#)
And DateValue(RecordDate) <= NZ([forms]![frmPrint]![EndDate],#12/31/2999#);

To make this more efficient I would use the following where clause

WHERE RecordDate >= NZ([forms]![frmPrint]![StartDate],#1/1/2000#)
And RecordDate <
DateAdd("d",1,Nz([forms]![frmPrint]![EndDate],#12/31/2999#))

Then you don't need to force RecordDate to just the date portion. This gets
all records on or after midnight of the startdate and all before or equal
to EndDate at 23:59:59.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..