MS Access SQL Date Range Query

Question

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.

Are you using a SQL Server database as a back-end to MS Access? — STLDev
– STLDev, Commented Apr 4, 2017 at 14:08
@STLDeveloper - no. I intend to split the database and put the tables on a network share, password-protected, and provide the customers with .accde front ends that "know" the password. I don't have access to any kind of server-based delivery. — Eclectic
– Eclectic, Commented Apr 4, 2017 at 15:15

Steve Lovell · Accepted Answer · 2017-04-04 14:48:58Z

1

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])));

answered Apr 4, 2017 at 14:48

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cetin Basoz · Accepted Answer · 2017-04-04 17:01:56Z

1

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).

answered Apr 4, 2017 at 17:01

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

2 Comments

Eclectic Over a year ago

Thanks! The values will be passed in as parameters - this is the bare minimum I could use to make a standalone query that illustrated my issue. Explicitly formatting the date strikes me as a good practice, although I think that mm/dd/yyyy is the default format in the U.S.

Cetin Basoz Over a year ago

In any case pass them as parameters and you should be good to go. Note that the values do not use >= and <= but > and < (one's end could be other's start, that wouldn't be considered as overlap).

Collectives™ on Stack Overflow

MS Access SQL Date Range Query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related