CODEWITHSUNDEEP
sqlms-access
Eclectic
Eclectic

Reputation: 33

MS Access SQL Date Range Query

I am working on a classroom reservation tool. A core component is the ability to compare the requested date range to the existing reservations, to ensure that there is no overlap. I've read through several date range related questions here, and studied Salman's explanation and implementation of Allen's interval algebra ( SQL Query to Find Overlapping (Conflicting) Date Ranges ) until I understood it. Here's a stripped-down version of what I came up with. 

tblRooms
roomID      room
5           110
30          178

tblReservations
reservedID  fkRoom dateIn     dateOut
1           5      3/10/2017  3/15/2017
2           5      3/1/2017   3/3/2017
4           5      4/1/2017   4/30/2017

SELECT DISTINCTROW tblRooms.roomID, tblRooms.room
FROM tblRooms LEFT JOIN tblReservations 
ON tblRooms.roomID = tblReservations.fkRoom
WHERE NOT Exists (
  SELECT DISTINCT tblRooms.roomID 
  FROM tblRooms 
  WHERE ((tblReservations.[dateOut] >= #3/3/2017#) 
  AND (#3/9/2017# >= tblReservations.[dateIn])));

I'm getting inconsistent returns. These dates will exclude room 110, as they should. Other test input (#3/4/2017# and #3/10/2017#, #4/1/2017# and #4/14/2017#) won't. I've tried combinations of "WHERE NOT (...", "WHERE Exists () = False", etc.

I work on a highly restrictive network, where I can't pull in templates at will - my only options when I create a database are "Blank" and "Web", so I've got to roll my own on this. I appreciate any assistance.

Upvotes: 0

Views: 95

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

For a reservation check query you would do this:

select ...
from tblRooms room
where not exists
( select *
  from tblReservations r 
  where r.fkRoom = room.roomId and 
        end > r.[datein] and start < r.[dateout] );

BUT the important part is, pass those end and start as parameters instead of hardcoded values like you did. With hardcoded values you are always open to get wrong results or error. For example what is:

#3/9/2017# really? Its interpretation would depend on regional settings (I am not an access programmer so I might be wrong).

Upvotes: 1

Steve Lovell
Steve Lovell

Reputation: 2564

Can you try the following:

SELECT DISTINCTROW tblRooms.roomID, tblRooms.room
FROM tblRooms
WHERE NOT Exists (
  SELECT 1
  FROM tblReservations 
  WHERE
  tblReservations.fkRoom = tblRooms.roomID
  AND ((tblReservations.[dateOut] >= #3/3/2017#) 
  AND (#3/9/2017# >= tblReservations.[dateIn])));

Upvotes: 1

Related Questions