Filter Query to display who is supposed to be working based on time of day

[SFGiants21256]

Hello,



I'm trying to filter a query based on the current time and when an employee is supposed to be at work. The difficult variable is when the persons shift goes over midnight and they are supposed to be off. I can currently only get the filter to work correct based on the day and not the time. For example, if the shift is from 1830-0630 it is great until the days you are supposed to be off work. If I filter it by day then when we cross over midnight the person is supposed to be at work. Therefore it does not calculate who is supposed to be at work correctly.

Below is a small sample of the database.

I should add that I'm not very good at access.


Accouuntability_V2.zipAccouuntability_V2.zip

[ranman256]

To add time, you must use the ENTIRE date/time number (1/2/15 1am) - (1/1/15 11am) THEN the calculations work correctly.
But filtering on a single time is tricky because it must be EXACT to the second, if you record seconds. Ranges maybe?

[SFGiants21256]

Would a range help to see if the current time is within that range? I tried: Time() between [shift start] and [shift end] but that didn't work.

[SFGiants21256]

Here is an earlier version of the database.