Software

Ask something
Discussion Locked

Hour/minute queries in Access

0 helpful
3 replies

by sharon_minick

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How do you get a query to work with AM and PM time? For instance, I need to query a city database that lists delivery times for all restaurants. If I need a list of all restaurants that deliver after 10:30 PM, I can't bring up the ones that deliver in the AM times after midnight. I can't get a query to work even if I change all the restaurant hours in the database to military time.

Thanks.

3 replies

by sl-campbell

In order to see what is happening, test this by building a new table called Deliveries and add the fields:
CityID text, ResturantID text, DeliveryDateTime date/time.

Add some data for 2 different months where data for each month spans over 2 days, with different dates and times.

In the query viewer select SQL VIEW and copy this in and then select DESIGN view to see what this represents in the query builder:

SELECT CityID, ResturantID, DeliveryDateTime AS [Deliveries between 10:30 PM and 11:59 AM]
FROM Deliveries
WHERE (((DeliveryDateTime ) Between [Start Date] And [End Date]) AND ((TimeValue([DeliveryDateTime ])) Not Between "22:30:00" And "12:00:00"));

This will invoke you to enter a start and end date but you can remove this if you just always want all dates.

This discussion is closed to new replies.