Is there a query for this

Hi All
Front end vb6, back end Access.
Program makes reservations for my library.
4 rooms, length of reservation will be in half hour increments
I need to be sure two events are not booked with overlapping times.
Right now I retrieve a recordset as follows

Date, Room, StartTime, StopTime ( sorted by StartTime)
So a typical recorset with two events scheduled might look like:

12/5/2008 Druker Auditorium 9:00am 10:30am
12/5/2008 Druker Auditorium 1:30pm 4:00pm

Would there be any type of query to be sure another reservation would not
confilct.
e.g. A person would try to book an event from 3:00pm until 6:00pm


Thank you
Harold

The logic is that 2 events clash if:
A starts before B ends, AND
B starts before A ends.

You can see this meets all cases if you diagram it on a piece of paper,
e.g.:
A ------------------------------
B ------------------
or:
A ----------------------------------
B --------
and so on.

Use record-level validation (the Form_BeforeUpdate event if you were working
in Access) to see if there is any other record for which this condition is
met

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"H. Druss" wrote in message
...
Hi All
Front end vb6, back end Access.
Program makes reservations for my library.
4 rooms, length of reservation will be in half hour increments
I need to be sure two events are not booked with overlapping times.
Right now I retrieve a recordset as follows

Date, Room, StartTime, StopTime ( sorted by StartTime)
So a typical recorset with two events scheduled might look like:

12/5/2008 Druker Auditorium 9:00am 10:30am
12/5/2008 Druker Auditorium 1:30pm 4:00pm

Would there be any type of query to be sure another reservation would not
confilct.
e.g. A person would try to book an event from 3:00pm until 6:00pm

Hi Allen
Perfect! I've applied the logic to a vb Function.
Thank you very much.
Harold

"Allen Browne" wrote in message
...
The logic is that 2 events clash if:
A starts before B ends, AND
B starts before A ends.

You can see this meets all cases if you diagram it on a piece of paper,
e.g.:
A ------------------------------
B ------------------
or:
A ----------------------------------
B --------
and so on.

Use record-level validation (the Form_BeforeUpdate event if you were
working in Access) to see if there is any other record for which this
condition is met

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"H. Druss" wrote in message
...
Hi All
Front end vb6, back end Access.
Program makes reservations for my library.
4 rooms, length of reservation will be in half hour increments
I need to be sure two events are not booked with overlapping times.
Right now I retrieve a recordset as follows

Date, Room, StartTime, StopTime ( sorted by StartTime)
So a typical recorset with two events scheduled might look like:

12/5/2008 Druker Auditorium 9:00am 10:30am
12/5/2008 Druker Auditorium 1:30pm 4:00pm

Would there be any type of query to be sure another reservation would not
confilct.
e.g. A person would try to book an event from 3:00pm until 6:00pm