Double Booking Problem (1 Viewer)

[esymmonds]

I'm trying to make a query that will show what rooms in my hotel are available between 2 dates.

The 2 tables that will be used are:

Booking
book_room ------ foriegn key, room_no
date1 -------- Date of booking, each night of booking has a record
custno

Room
room_no
smoking -------- if room is smoking
bed ---------- Single, double, twin


So I have a form where the user enters arrival and departure dates, choice of room, smoking or none smoking. Then I want a query to see which rooms AREN'T booked during this period.

I am, at the momment working with the query:

SELECT DISTINCT room.room_no, room.smoking, room.bed, booking.date
FROM room LEFT JOIN booking ON room.room_no = booking.book_room
WHERE (((room.smoking)=[Forms]![avail]![smoke]) AND ((room.bed)=[Forms]![avail]![bed]) AND ((booking.date) Not Between [Forms]![avail]![date1] And [Forms]![avail]![date2])) OR (((room.smoking)=[Forms]![avail]![smoke]) AND ((room.bed)=[Forms]![avail]![bed]) AND ((booking.date) Is Null));

But at the moment it is returning a booked room when the room is booked in the future. I need to some how tell it that if the room is booked between those dates then remove every instance of the room from the search.

Can anyone help?

Emma

 

[esymmonds]

Thanks for looking at my post, but I got it working now but I don't know why!!

Never mind, isn't it nice when things just work!

Emma