Jump to content

Flag a time range overlap


This topic is 1834 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Greets, everybody! Please refer to the attached.

What I want to do is set a conditional formatting calc so that if there's an overlap of time for a reserved court (as shown in the attachment), the records within that overlap will turn red to signal the user that there's a time conflict. How do I pull this off? (I have yet to figure how to prevent that from happening in the first place, but I'm guessing I could re-engineer the needed calc so that when a new record is created, it will search for a time conflict before being recorded.)

I'm guessing that the answer involves converting the date and time into a concatenated string of numbers then using the MIN and MAX calcs to see if they fall within a range; I also suspect that a self-join table (or e-SQL?) will be needed as well, but I'm just stymied on how to move forward with this thing.

TIA for your help!

Screen Shot 2019-03-22 at 10.39.24 AM.png

Link to comment
Share on other sites

A self-join of your table would be convenient, say:

Reservations::Date = Reservations 2::Date
AND
Reservations::TimeIn ≤ Reservations 2::TimeOut
AND
Reservations::TimeOut ≥ Reservations 2::TimeIn
AND
Reservations::ReservationID ≠ Reservations 2::ReservationID

If there are any related records from Reservations 2, then there's a conflict.

  • Like 1
Link to comment
Share on other sites

This topic is 1834 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.