Double Booking Query

[Andy Day]

At present I am developing a database for room bookings at work.

The point I am at is to put in a query to check for double bookings. So far this is fine, if a room does not overlap on the time, it says there isn't a clash, and if there is an overlap, it says there is an overlap.

But, what I'd like is for the query to return an overlap if two bookings are made within 30 minutes of each other. Example, if a meeting in room 1 ends at 13:00 and another starts at 13:15 in the same room, an overlap would be returned. This is needed as sometimes the layouts of rooms need adjusting and so staff need time to check the rooms.

The part fo the query that deals with times is as follows (I am using the same table twice in the query to compare bookings).

NoClash: (TblMeetingAppoint_1.MeetingStart>=TblMeetingAppoint.MeetingEnd) Or (TblMeetingAppoint_1.MeetingEnd<=TblMeetingAppoint.MeetingStart)

I've tried to use DateAdd to modify times, but whenever I do, the "n" part of DateAdd to tell DateAdd I want to modify the minutes, keeps on being changed to ["n"] when I save the query. When the query is run, the query then asks for the value of n.

Should I be using DateAdd or something else? Any help would be greatly appreciated!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Performance Testing .NET Web Apps [MSPRESS]
http://www.eggheadcafe.com/tutorial...4af-00387a2f471e/performance-testing-net.aspx

 

[John Spencer]

(TblMeetingAppoint_1.MeetingStart>=DateAdd("n",30,TblMeetingAppoint.MeetingEnd))
Or
(TblMeetingAppoint_1.MeetingEnd<=DateAdd("n",-30,TblMeetingAppoint.MeetingStart))

That might be overkill since you are adding 30 minutes to the End time and
subtracting 30 minutes from the begin time. Which has the effect of expanding
the meeting time by 60 minutes. Perhaps you only need to increase the meeting
end time.

If that does not work, please post the entire SQL of the query that is not
working. (View: SQL and then copy and paste).
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

At present I am developing a database for room bookings at work.

The point I am at is to put in a query to check for double bookings. So far this is fine, if a room does not overlap on the time, it says there isn't a clash, and if there is an overlap, it says there is an overlap.

But, what I'd like is for the query to return an overlap if two bookings are made within 30 minutes of each other. Example, if a meeting in room 1 ends at 13:00 and another starts at 13:15 in the same room, an overlap would be returned. This is needed as sometimes the layouts of rooms need adjusting and so staff need time to check the rooms.

The part fo the query that deals with times is as follows (I am using the same table twice in the query to compare bookings).

NoClash: (TblMeetingAppoint_1.MeetingStart>=TblMeetingAppoint.MeetingEnd) Or (TblMeetingAppoint_1.MeetingEnd<=TblMeetingAppoint.MeetingStart)

I've tried to use DateAdd to modify times, but whenever I do, the "n" part of DateAdd to tell DateAdd I want to modify the minutes, keeps on being changed to ["n"] when I save the query. When the query is run, the query then asks for the value of n.

Should I be using DateAdd or something else? Any help would be greatly appreciated!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Performance Testing .NET Web Apps [MSPRESS]
http://www.eggheadcafe.com/tutorial...4af-00387a2f471e/performance-testing-net.aspx