Searching Between Dates in MS Access

We use an MS Access database to track our rentals for golf carts. Customers can call and request carts for use for a varying number of days.

We input the requests into the database using the scheduled delivery date, scheduled pickup date as well as what kind of carts are requested and the quantity of those carts. The majority of our rentals are for 4 days (deliver on a Friday and pick up on a Monday), however we do have some deliveries that extend to weeks or even months.

I am trying to figure out a way to be able to search the requests to determine how many carts of a specified type we have scheduled to be out on a specified date so we don't overextend our requests. I'm drawing a blank on how to do it. I can search the requests based on a date range but only if those dates are either the scheduled pickup or scheduled delivery date not if a cart will be out during that date range that begins or ends after the range specified (if that makes sense).

I tried creating a simple query and searching by date range but it only gives me the carts that meet those dates specifically and not any that will be out within that range.

Solution

Retrieving records where a specified range and rental range overlap means testing for two conditions:

one or both ends of specified range fall within rental range
one or both ends of rental range fall within specified range

Consider:

SELECT * FROM table
WHERE [enter start date] BETWEEN DeliveryDate AND PickupDate 
   OR [enter end date] BETWEEN DeliveryDate AND PickupDate
   OR DeliveryDate BETWEEN [enter start date] AND [enter end date] 
   OR PickupDate BETWEEN [enter start date] AND [enter end date];