Reservation Query

[Guest]

I always second-guess myself when it comes to date math, and I'm hoping that
you guys can make sure I haven't missed anything.

I'm building an Access DB that houses reservation information for a hotel.
Each record in the reservation master table has a CHECK_IN date field and a
CHECK_OUT date field.

I want to build a search query that allows the user to see which rooms are
available if someone wants to stay for a given day. The name of the room is
in a room master table.

Below is the query I set up, where "datIn" and "datOut" are the check in and
check out dates being examined. This query should return any rooms available
on the days in question, or return nothing if the place is booked.

strSQL = "SELECT R.ROOM_NAME FROM ROOM_MASTER AS R " & _
"WHERE R.ROOM_ID NOT IN(SELECT M.ROOM_ID FROM RESERVATION_MASTER AS M
WHERE M.CANCEL=False AND (" & _
"(M.CHECK_IN BETWEEN #" & datIn & "# AND #" & DateAdd("d", -1,
datOut) & "#) " & _
"OR (M.CHECK_IN<#" & datOut & "# AND M.CHECK_OUT>=#" & datOut & "#)))"

I put in the DateAdd() section because it is possible for someone to check
into a room on the same day that a prior guest has checked out.

The query SEEMS to work with the data I have (~130 rows) but I'm not sure I
have every possible scenario represented in that data. Does the query look
like it would handle the type of logic I need? Can anyone suggest a simpler
method? Thanks.

 

[John Spencer]

Basically, you are looking to see if there is an overlap between two date
ranges. There is an overlap when

DateIn < Check_Out And DateOut >= Check_In