I have developed a hotel availability chart. The user selects their arrival and departure dates from drop down lists. This queries the database to show any rooms that are available for the dates.
My database structure has the following tables:
My query currently looks like this:
SELECT *, 'images/pic-room0' & rooms.roomID & '.jpg' AS roompic from rooms WHERE roomID NOT IN(Select bookingroom FROM bookings WHERE bookingarrival >= #" & arrivalmonth & "/" & arrivalday & "/" & arrivalyear & "# AND bookingdeparture <= #" & departmonth & "/" & (departday) & "/" & departyear & "#)
However this shows rooms as available where the arrival date is prior to the date selected in the form. I just can't get my head round this, any help would be much appreciated.
Thanks in advance