Message Box

[Alex Motilal]

Hello everybody, I am developing a Hotel Booking Database for a new Hotel. I do have the following tables among other tables: 1)tblGuests GuestID (PK - Auto Number) and other fields such as Name and address 2) tblRooms RoomNumber (PK - Text) 3) tblGuestBooking GuestID (Child) RoomNumber (Child) dateOfBooking (Date) dateOfLeaving (Date) I am creating a Form for Bookings. When a room number is selected in the Combo Box, if the room is occupied, I want a Message box to appear informing that the room is occupied and instructing to select another room. The scenarios are as follows: 1) Since the Hotel is new, some rooms are yet to be occupied, so that both the dateOfBooking and dateOfLeaving fields may be empty. Status -Vacant 2) Some rooms might have been occupied and yet to be vacated. Status- Occupied 3) Some rooms might have been booked and vacated. Status – Vacant I tried to create a query. It is successful. However, for a particular room earlier booked and vacated and presently under occupation, it appears two times, one for Vacant and the other for Occupied. I want only one status for each room. I think some of the experts can help me to create a function or the correct query. Alex

[pbaldy]

Would this method help?

http://www.baldyweb.com/OverLap.htm

You would need an additional criteria for the room number.