I have a db used to track our company pool car inventory, including check-out/check-in information. Vehicles can be reserved for future use “multiple” times as longs as the vehicle is checked back “in” before the next check-out date. Example:
Reservation #1: Checkout 11/22/11, Return Date 11/26/11
Reservation #2: Checkout 11/29/11, Return Date 12/4/11
Reservation #3: Checkout 12/5/11, Return Date 12/12/11
Currently we have 12 vehicles in the pool, and some have may have no current reservations tied to them. What I am trying to do is set up a search form whereby the person responsible for issuing the vehicles can enter 2 dates (check-out and check-in) and get a list of all vehicles that are available during that period of time. All of my previous seach forms are based on looking for data that falls between two dates, however I need to be able to set a system were any reservations that fall anytime during the search (check-out and check-in) dates would be EXcluded, not Included. This would also include vehicles with no current reservation dates.
I have set up a regular query with the pool car number (primary key), and [DateCheckedOut] and [DueDate] fields, both “date” fields obviously, but now I am drawing a blank. I have been able to block out the entries where the check-out date does not equal the date entered, same with the due date, but cannot figure out how to do both at the same time, and most importantly, including all of the dates between the two.
As always I appreciate the assistance provided.