between help

[HOMMER]

I want to query between 7:00 am and 8:00 am on my list of time and date stamps.
I have a table filled of numbers each number has a date time stamp. I want all the records between 7:00 am and 8:00 am over a date range. When I do the date range it brings all the records between the dates not between the times...

 

[dhookom]

If I understand correctly, you want to filter the TimeValue() of your field:
WHERE TimeValue([DateTimeField]) Between #7:00 AM# and #8:00 AM#

Duane
MS Access MVP

 

[HOMMER]

Yes all the data between 1/1/03 7:00 am and 9/1/03 8:00 am only...

 

[dhookom]

If you want all the times between the start and end then
Between #1/1/03 7:00 am# and #9/1/03 8:00 am#
Should work.

Duane
MS Access MVP

 

[r937]

"all the records between 7:00 am and 8:00 am over a date range"

duane, that cannot be done with a simple BETWEEN test on the datetimes, because it inludes all time for days in between the start and end dates

hommer, try this --

where datetimefield between #2003-01-01# and #2003-09-02#
and format(datetimefield,"hh:mm&quot between '07:00' and '08:00'

notice 2003-09-02 on the top end, which is midnight on the 2nd, so that the first half of the WHERE clause is satisfied, and thus the 7:00-8:00 time range in the morning of the 1st will be included

rudy

http://r937.com/

 

[dhookom]

Good catch Rudy...
Rather than converting a field value to a string for the time comparison, I would rather use:
WHERE DateTimeField Between #2003-01-01# and #2003-09-02#
AND TimeValue([DateTimeField]) Between #7:00 AM# and #8:00 AM#

Duane
MS Access MVP