Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-02-2016, 11:54 AM   #1
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Query to identify if a time period is between times that pass through midnight?

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

keeper is offline   Reply With Quote
Old 06-02-2016, 12:16 PM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

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.
plog is offline   Reply With Quote
Old 06-03-2016, 08:30 AM   #3
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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.

keeper is offline   Reply With Quote
Old 06-03-2016, 08:44 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

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.
plog is offline   Reply With Quote
Old 06-03-2016, 09:03 AM   #5
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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...
keeper is offline   Reply With Quote
Old 06-03-2016, 09:35 AM   #6
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

You can use the TimeValue (http://www.techonthenet.com/access/f.../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")
plog is offline   Reply With Quote
Old 06-03-2016, 11:00 AM   #7
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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

keeper is offline   Reply With Quote
Old 06-03-2016, 11:03 AM   #8
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

Please post sample data in my format that do not work.
plog is offline   Reply With Quote
Old 06-03-2016, 11:13 AM   #9
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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
keeper is offline   Reply With Quote
Old 06-03-2016, 11:20 AM   #10
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

Quote:
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?
plog is offline   Reply With Quote
Old 06-03-2016, 11:26 AM   #11
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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.
keeper is offline   Reply With Quote
Old 06-03-2016, 11:33 AM   #12
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,419
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query to identify if a time period is between times that pass through midnight?

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.
plog is offline   Reply With Quote
Old 06-03-2016, 11:36 AM   #13
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

Yes , Many Thanks, i'll create a version for you.
keeper is offline   Reply With Quote
Old 06-03-2016, 12:08 PM   #14
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

As requested, hope this helps, many Thanks
Attached Files
File Type: zip SampleDB.zip (42.4 KB, 12 views)
keeper is offline   Reply With Quote
Old 06-03-2016, 12:22 PM   #15
keeper
Newly Registered User
 
Join Date: Jun 2016
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
keeper is on a distinguished road
Re: Query to identify if a time period is between times that pass through midnight?

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

keeper is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate period between 2 Times bee55 Modules & VBA 7 02-09-2015 04:58 AM
Midnight Time calculation Help.... Big_Rob Reports 19 09-30-2011 07:38 AM
time spanning midnight pmvman Queries 2 11-09-2010 12:33 PM
Validation and Entering Times after Midnight thru a form Researcher Forms 2 01-31-2007 01:03 AM
How to Calculate times across midnight. shoggy Queries 3 01-15-2007 04:19 AM




All times are GMT -8. The time now is 07:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World