How do I run a query between two dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a database designed to book rooms i have a query to find out which rooms
are free on specific dates however it doesn't work. Because a room can be
booked for more than one day when the booking is made it has a 'start of
booking date' and 'end of booking date'.
However when i run a query to find out about free rooms if i type a date
that is between the 'start of booking date' and 'end of booking date' of a
booking then it shows that the room is free even though it isn't.
How do i make it so that it will bring up bookings that span the date that
the user has searched for?
 
Hi,


The list of rooms that are busy:


SELECT r.RoomID
FROM Reservations AS r
WHERE r.Starting <= [Param Ending] AND r.Ending >= [Param Starting]



so:


SELECT s.RoomID
FROM Rooms As s
WHERE s.RoomID NOT IN(
SELECT r.RoomID
FROM Reservations AS r
WHERE r.Starting <= [Param Ending] AND r.Ending >=
[Param Starting] )



Should list the rooms that are free.



Hoping it may help,
Vanderghast, Access MVP
 
On the premise of Give a fish vs. Teach to fish, try the following:

1. On a piece of paper, draw a line from edge to edge of the page. This is
your timeline.

2. At 25% of the distance, make a vertical tick mark. Do same at 75%.
Assume that everything between those two lines is a booked room time span.
Left tick is start date, Right tick is end date.

3. Now start making other Use Case lines above the base timeline that
represents new requests for room. Here are the possibilities:
a. New booking starts prior to booked start date, ends prior to booked start
date
b. New booking starts prior to booked start date, ends after bookedstart
date, but before booked end date
c. New booking starts after booked start date, ends before booked end date
d. New booking starts after booked start date, ends after booked end date
e. New booking starts after booked end date, ends after booked end date

Analyzing these use cases, you will discover that cases a and e do not
matter to you, as they do not conflict with the booked room. So, turn your
attention to only cases b, c, and d. Use the Between...And construct within
your queries to limit to these cases.
 
Back
Top