Tell us more about how this data is stored in your table.
What fields do you have?
a) Two Date/Time fields that contain a date and a time?
b) Two Date/Time fields that contain a time value only, so that if the end
time is before the start time, it indicates the next day?
c) Two Text fields that contain the times as text?
d) One Text field that contains text such as:
6:00:00 PM to 5:00:00 AM
Next, are you wanting to calculate the actual number of hours of overlap
between the criteria range and the range in each record, or are you just
wanting to select any records that have a partial (or complete) overlap?
If you just want overlapping records, the essential logic is that 2 events
overlap if both:
- A starts before B ends, and
- B starts before A ends.
If you want to calculate the amount of time overlapping each record and the
criteria, the answer end up with fairly involved expression, using:
- IIf() to determine whether the end time is the next day;
- DateAdd() to add 24 hours if it does end the next day;
- Possibly DateDiff("n", ...) to get the difference in minutes;
- More IIf() expressions to determine where the overlaps are.
One more issue: are these fields required, or could they be null? If there
are nulls, how do you want them handled?
