I'm working on an employee schedule. I have folks start and end times as well as break and lunch times.
I'm trying to create 1/0 for working/not working in 15-minute intervals throughout the day.
My equation looks to say if the interval is less that the person's start time, greater than the person's end time or equal to a break or lunch time, then 0 (they aren't working during that interval), else 1 (they are working during that interval).
On many intervals, the following equation works.
Working: IIf(([Time Intervals]![IntervalStart]<[tblSchedules]![StartTime]) Or ([Time Intervals]![IntervalStart]>[tblSchedules]![End]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![AMBreak]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch1]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch2]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch3]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![PMBreak]),0,1)
However, [Time Intervals]![Interval Start] might be 7:45 AM and [tblSchedules]![StartTime] might be 7:45:00 AM and Access doesn't realize these should match. The format on both tables is Date/Time, Medium Time.
I've copied into Excel and back and I still get these issues.
I've tried using a rounding function and thought it may have worked, but again it seems to be a mix and match of these types of discrepancies so rounding isn't always the answer (in fact, sometimes it took a correct answer and turned it into the wrong answer).
I can't be the only one trying to compare times in Access, but I haven't found a post that addresses this directly.
Any help is appreciated.
Thanks in advance!
I'm trying to create 1/0 for working/not working in 15-minute intervals throughout the day.
My equation looks to say if the interval is less that the person's start time, greater than the person's end time or equal to a break or lunch time, then 0 (they aren't working during that interval), else 1 (they are working during that interval).
On many intervals, the following equation works.
Working: IIf(([Time Intervals]![IntervalStart]<[tblSchedules]![StartTime]) Or ([Time Intervals]![IntervalStart]>[tblSchedules]![End]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![AMBreak]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch1]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch2]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch3]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![PMBreak]),0,1)
However, [Time Intervals]![Interval Start] might be 7:45 AM and [tblSchedules]![StartTime] might be 7:45:00 AM and Access doesn't realize these should match. The format on both tables is Date/Time, Medium Time.
I've copied into Excel and back and I still get these issues.
I've tried using a rounding function and thought it may have worked, but again it seems to be a mix and match of these types of discrepancies so rounding isn't always the answer (in fact, sometimes it took a correct answer and turned it into the wrong answer).
I can't be the only one trying to compare times in Access, but I haven't found a post that addresses this directly.
Any help is appreciated.
Thanks in advance!