Difficult Booking System Availibility Query

[lewis.gib1]

I am wondering if anyone is able to help.... I am creating a hotel booking system with linked tables as follows:

Guest Details - Bookings - Bookings Details - Staff - Rooms

Each with foreign keys which link them. The bookings table links Employee, Guest and Booking Detail, Booking Detail then links to rooms. The only other data in the bookings table is the booking date. All other details for the booking.... room number, Arrival date, departure date etc are in the booking detail table. I am in the process of creating an interface and I need a query/macro which will find the availability between two dates and show which rooms are available on certain dates

I.e there is no booking in that room on any of those dates.



I understand that:
The existing booking must arrive before (enter arrival date) and depart on or before (enter arrival date)
OR
The existing booking must arrive after (enter departure date) and depart after (enter departure date)


I currently have a query which shows all the rooms which are unavailable for the set period. Is there any way that I can use a macro to show the opposite results?? I have tried using NOT but it doesn't work. I also need to present it on a form for the interface

This is for an examined piece of work so
Any help is appreciated, thanks

[orange]

see this thread for ideas

also review the Similar Threads at the bottom of the page.