I have a booking system where events are booked on a particular date at a particular time. The events are often late in the evening and end early am the next day. I need to ensure that an event is not booked for the same venue at a time that may conflict with an existing booking. The problem I am finding is the fact that 3:00 am is later than 10pm the night before. I need a way using a relationship to catch potential overlapping times. Could anyone help please, it's doing my head in and I'm sure there is a straightforward solution?

Use a timestamp instead of date and time (or calculate the timestamp from the entered date and time).

The relationship needs to be:

Events::VenueID = Events 2::VenueID

AND

Events::Start ≤ Events 2::End

AND

Events::End ≥ Events 2::Start

AND

Events::EventID ≠ Events 2::EventID

In thinking about the method for comparing times, consider (search for) the events which start one day and end a day later. Then continue your script to compare (in a loop) all events in that set to events which occur during that following day.

No, do what Comment says, in both using the time stamp AND using the multi-predicate relationship to do the heavy lifting.

A "time" by itself is practically* useless for anything. Hence "timestamp" was invented.

* Yes, yes, there are many practical uses for Time fields. This isn't one of them.

Thanks very much. I agree with the relationship stance, and this is what was driving me a bit nuts. I thought about timestamp but wasn't sure how best to use it. I do not want to script this, so I think Comment's comments (he he) are valid here.