Query to identify if a time period is between times that pass through midnight? (1 Viewer)

keeper · Jun 2, 2016

Hi

I'm in the process of designing a database to identify if work can be performed during a time period.
I also have to take into account the day of the week but i've done this by testing against the start days allowed which are stored in a table.

So for example, a department allows work between 18:00 - 08:00.

I have a start time in the format dd/mm/yyyy hh:nn called start_date
and a end time in the format dd/mm/yyyy hh:nn called end_date

The solution I currently use is to think the wrong way round so i'm testing if the start time is between 08:00 and 18:00 if not thats OK.
I then perform the same on the end date, plus I calculate if the HOURDIFF is greater than the allowed window i.e 14hours.

Is their a better way to test if times are between a range that cross midnight?

Many Thanks

plog · Jun 2, 2016

TestTime>=StartTime AND TestTime<=EndTime

No need for any calculations of hours or midnight. Just see if the time you are testing falls between the start and the end.

keeper · Jun 3, 2016

I also need to take into account the end date,
So the time of the change must not be before 6PM
And the end time must be before 8AM
So between Start Time 1/5/2016 18:00 And End Time 1/5/2016 08:00 is OK.
Start Time 1/5/2016 17:59 And End Time 1/5/2016 08:00 is FAIL
Start Time 1/5/2016 18:00 And End Time 1/5/2016 08:01 is FAIL

I have a work start date & time and an end date an time, I need to identify if the two fall with the allowed timescales.
If it goes over the allowed number of hours I use HOURDIFF to calculate if its gone over the allowed 14Hr period but I calculate in minutes.

plog · Jun 3, 2016

Huh? You initially had a time you needed to see if it fell within a period. Now you have 2 time ranges you need to see about? Lost.

Please demonstrate what you need with data. No explanations. Use this format:

ActualWorkStart, ActualWorkEnd, AvailableStart, AvailableEnd, Result

ActualWork is when the work will be performed
AvailableStart is when they are allowed to get in there
Result is what you expect your query to show

Provide enough data to cover all cases, and no more explanations. Show me with data.

keeper · Jun 3, 2016

ActualWorkStart, ActualWorkEnd, AvailableStart, AvailableEnd, Result
1/5/2016 18:00 2/5/2016 08:00 18:00 08:00 = Pass
1/5/2016 17:59 2/5/2016 08:00 18:00 08:00 = Fail
1/5/2016 18:00 2/5/2016 08:01 18:00 08:00 = Fail

So ActualWorkStart has to be after 18:00 and ActualWorkEnd before 08:00 next day.
And any times in-between must provide the correct Pass/Fail

Many Thanks Hope the format is OK...

plog · Jun 3, 2016

You can use the TimeValue (http://www.techonthenet.com/access/functions/date/timevalue.php) function to extract out the time from a full Date/Time. Then you can compare those time values to see if one is before/after the other.

I think this code will satisfy those 3 examples:

Code:

Result: iif(TimeValue(ActualWorkStart)<TimeValue(AvailableStart) OR TimeValue(ActualWorkEnd)>TimeValue(AvailableEnd), "Fail", "Pass")

keeper · Jun 3, 2016

Mainly works

But those after Midnight but before 08:00 on the same day fail when they should
pass. I wonder if I need to split the time check before midnight and after?

CHANGE_START CHANGE_END Result
03/06/2016 22:00:00 04/06/2016 08:00:00 Pass
03/06/2016 22:00:00 04/06/2016 04:00:00 Pass
04/06/2016 03:00:00 04/06/2016 17:00:00 Fail
04/06/2016 07:30:00 04/06/2016 18:30:00 Fail
07/06/2016 02:00:00 07/06/2016 05:00:00 Fail
07/06/2016 08:30:00 08/06/2016 00:20:00 Pass
07/06/2016 10:30:00 07/06/2016 15:00:00 Pass
08/06/2016 10:00:00 11/06/2016 23:00:00 Fail
08/06/2016 10:00:00 08/06/2016 17:30:00 Pass
08/06/2016 17:30:00 09/06/2016 01:00:00 Pass
09/06/2016 02:00:00 09/06/2016 05:00:00 Fail
13/06/2016 10:00:00 15/06/2016 23:00:00 Fail

plog · Jun 3, 2016

Please post sample data in my format that do not work.

keeper · Jun 3, 2016

ActualWorkStart, ActualWorkEnd, AvailableStart, AvailableEnd, Result
09/06/2016 02:00 09/06/2016 05:00:00 18:00 08:00 Fail

Sorry Plog, I didn't move the posts honest i appreciate your help, A change can start and end at any time, The important element is it must be somewhere after 18:00 one day and end no latter than 08:00 the next day which provides a 14Hr work window.

Many Thanks

plog · Jun 3, 2016

it must start after 18:00 one day and end no latter than 08:00 the next

Is that 100% accurate? Think about it--I tweaked it slightly.

Is the time frame always at 18:00 one day and 8:00 the next? Or could that ever move around?

keeper · Jun 3, 2016

In practice it could be different, however I keep the allowed start and end times in a table and refer to the fields.

However mainly its 18:00 - 08:00 so I want to focus on these times to ensure every test is correct in this timeframe . I'm finding that mostly what I try works, but certain times just catch the query out.

plog · Jun 3, 2016

Can you share your database so i can see how it all works together? Preferably just a stripped down version with the data I need and a few sample recrods.

keeper · Jun 3, 2016

Yes , Many Thanks, i'll create a version for you.

keeper · Jun 3, 2016

As requested, hope this helps, many Thanks

keeper · Jun 3, 2016

Please use the query "Test Query" I forgot to delete the other which links to old tables :banghead:

plog · Jun 3, 2016

Perfect, but much harder than I though. I will have to work on this on Monday, but I will get a solution for you.

keeper · Jun 4, 2016

Many Thanks, bit of a brain teaser than looks easy enough but doesn't behave as expected, I've also added in an HRSDIFF because if that exceeds 840mins it means its also failed or spread over multiple days because 18:00 to 08:00 is 840mins.

plog · Jun 5, 2016

Got it. That over midnight consideration required so much logic that I couldn't fit it just into a query, so I made a function to do it. You pass your four values (WorkStart & WorkEnd Date/Times and your availablity times) and it does all the logic for you and returns Pass/Fail.

Attached is a database with a query (Validation) that uses your 2 tables and does the checking for you. The function itself is in the Validation module. Let me know if you don't understand it all or if I screwed up somehow. And be sure to thoroughly test it just to make sure.

keeper · Jun 5, 2016

Hi Plog

Many thanks, All the records are returning a value of "Fail"?
If I look at the output
957 should = Pass
949 should = Pass
944 should = Pass
943 should = Pass
959 should = Pass
942 should = Pass

plog · Jun 5, 2016

That's how it runs for me (actually 942 is a fail because I changed the DAySTart/DayEnd times). This might have something to do with date settings. I'm in America so 3/5/2016 is March 5th 2016, not May 3rd 2016.

See if you can somehow tweak the date settings.

Query to identify if a time period is between times that pass through midnight? (1 Viewer)

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Registered User.

Attachments

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Attachments

Registered User.

Banishment Pending

Similar threads

Users who are viewing this thread