Date Range

[datalink]

In a resources table there are two date fields, StartInSeason and EndInSeason. In a second table, housing reservation data there are two other date fields, ReservationStartDate and ReservationEndDate.

I'd like a relationship between these two tables that can be used to test to see if any of the dates in the reservation date range fall outside of the InSeason date range. This relationship could then be used in scritpting or calculations to pull the correct set of billing rates and other house keeping tasks.

Any suggestions?

[Digital Life]

If you don't mind I'll call the start date Arrival and the end date the Departure.

Departure Date is worth talking about. In a hotel example a departure date is the day after the last date that the room is occupied. So the room (resource) is not technically "booked" on the departure date. You'll need to decide if this is the case in your system.

Given that caveat try this relationship:

Arrival ≥ StartInSeason

AND Arrival < StartEndSeason

AND Departure < StartEndSeason

In English the relationship is valid only if the Arrival date is on or after the Start of the season AND the arrival date is before the End of the season AND the departure Date is before the end of the season.

You'll need an additional check when entering dates to prevent the departure date from being before the arrival date.

Then in your calculations use IsValid or Not IsValid to determine if the relationship for a particular booking is valid or not. Hope this helps. JJ