Weird Behavior on Time

[wrayman]

I am just building a simple database for ordering glucose readings.

glucose.accdb

I have three queries to start with.



Morning Between #8:00:00 AM# And #12:00:00 PM# (Criteria for Time Field, works fine returns correct data)
Afternoon Between #12:00:00 PM# And #6:00:00 PM# (Criteria for Time Field, works fine returns correct data)


Evening Between #6:00:00 PM# And #1:00:00 AM# Doesn't work right returns values outside the parameter

I have attached the database, hard for me to figure out what is happening.

Database also available here if the attachment didn't work (new to forum) http://mcltech.com/access/glucose.accdb http://mcltech.com/access/glucose.accdb

If you run the queries you will see what I mean, in the Evening Query there are times from everywhere

[ItsMe]

Maybe you need to include the date if you are going to use criteria from one afternoon to the next morning.

[ssanfu]

First off, "Date" and "Time" are reserved words in Access and shouldn't be used as object names. Plus, they are not descriptive - "Date" of what? "Time" of what??
(and no spaces in field names)

The problem is because you don't have a date component in the "Time" field. You are searching only on the "time" field and the ending time is in the next date (1 AM).
You should really only have one date/time field.

[ssanfu]

Maybe this:

[wrayman]

Yep, you all are correct, wasn't thinking.

Thanks ssanfu for providing me with the code.