Time criteria on a Date/Time field in a query

You do need to extract the time portion - but you can do it dynamically in the Query without altering the table structure.

Create a Query based on the table. In a vacant Field cell type

JustTheTime: TimeValue([datetimefield])

If the field contains #4/7/2015 11:30am# this calculated field will contain just #11:30am# (well, actually, it will be #12/30/1899 11:30:00am# but since that date is the zero point for date/times, the query will see it as just a time).

Actually its not strange at all. First, you have to understand that Access stores Date/Time values as a Double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion is a fraction of a day (so 6AM is .25)

So if you enter criteria as Between #9:00:00 PM# And #11:00:00 PM#, it will look for records with a 0 date (i.e. 12/30/1899). Similarly, if you filter by date and use BETWEEN #4/1/15 AND #4/30/15# it will not include records for 4/30/15 9:00 AM. Because the criteria is assuming no decimal value, so its ignoring everything after 4/3/15 00:00AM.

When you work with Pivot Tables, Crosstab queries or Reports, the wizard to create those items ask what interval you organize the data on. So the wizards will, behind the scenes< parse out the time value if that's what you select. In other words, Access does the heavy lifting for you. 

Once you understand how datetime values work and how Access does a lot of the work for you, it really hits how powerful Access is. If you you tried to write a C# program to do the same thing, it would take a LOT of coding.

The reason for the special Datatype IS so that all database functions will work.

You don't HAVE to separate out the time unless you want to treat the time independently. For example, if you wanted to find all morning appts irrespective of date.

But if you want to find all appts for the morning of 4/1 you just have to include the date along with the time.

Thanks for the reply.  That's strange that I should have to extract the time of a Date/Time field before I can use it as a criteria in a query, because a PivotTable will operate on it just fine without having to extract it.  For example, if I want to view an hourly count of events, all I have to do in PivotTable view is drop the Date/Time field in the Row area, right click on that area and select Properties, Filter and Group tab, and then group items by hour.  Then I can drop any other field in the Data area and boom, I have an hourly count of each event.  (this is one reason why I don't "upgrade" to Access 2013, hahahahaha)