UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query To Show Results For Times Either Side Of Midnight, Access 2010    
 
   
Dale_j1992
post Oct 7 2019, 10:02 AM
Post#1



Posts: 26
Joined: 24-September 16



Afternoon All,

Many thanks in advance for any help with this.

i am trying to do a live parts count on one of my forms using a query.

i have it working fine for 2 shifts

Earlies
Between "06:00:00" And "13:59:59"
Lates
Between "14:00:00" And "21:59:59"

However the code does not work for night shift and i have tried a few methods, i think its due to my date field in my query using the >Date() function so when it passes over after midnight its looking in the wrong range.

It should show all records between "22:00:00" And "23:59:59" before midnight.

Then after it passes midnight show yesterday "22:00:00" And "23:59:59" and Today "00:00:00" And "05:59:59"

I have something working on excel for this but not sure how to lay the code out in an access query.

i would be extremely grateful for any hints or solutions.

Kind regards






Go to the top of the page
 
arnelgp
post Oct 7 2019, 10:32 AM
Post#2



Posts: 1,479
Joined: 2-April 09
From: somewhere out there...


use Timevalue(Now()) as criteria of your query.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
nuclear_nick
post Oct 7 2019, 10:32 AM
Post#3



Posts: 1,789
Joined: 5-February 06
From: Ohio, USA


Do you have the date as well? You'll need to put both the date and time in the query, not just the time. So instead of just...
CODE
Earlies
Between "06:00:00" And "13:59:59"
Lates
Between "14:00:00" And "21:59:59"

… you need something like...
CODE
Earlies
Between "10/7/2019 06:00:00" And " 10/7/2019 13:59:59"
Lates
Between "10/7/2019 14:00:00" And "10/7/2019 21:59:59"
Overnight
Between "10/7/2019 22:00:00" And "10/8/2019 05:59:59"




--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 10:37 AM
Post#4


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


You're right, crossing midnight does make a difference.

So, your shifts are defined as 6:00AM to 2:00PM. 2:00PM to 10:00PM and 10:00PM to 2:00AM the following day. All three shifts START on the same day, but one ends on the next day, or date+1.

It would help to see the full SQL in this query, of course, but in the abstract, we could say that the general format for a shift is:

Date and start time plus eight hours.

Or, perhaps 2019/10/7 6:00:00AM plus eight hours, 2019/10/7 2:00:00PM plus eight hours, and 2019/10/7 10:00pm plus eight hours.

Perhaps you can write a criteria that uses this format, as opposed to specific end times for shifts.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 7 2019, 10:55 AM
Post#5


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


You're using
CODE
>Date()
? Or
CODE
Between "06:00:00" And "13:59:59"  Or Between "14:00:00" And "21:59:59"
?
Date should cross simultaneously as the time crosses to 00:00.

--------------------
Robert Crouser
Go to the top of the page
 
John Vinson
post Oct 8 2019, 01:03 AM
Post#6


UtterAccess VIP
Posts: 4,285
Joined: 6-January 07
From: Parma, Idaho, US


It's important to note that Dates and Times are NOT strings. Access stores timepoint data as a number, a Double Float count of days and fractions of a day since midnight, December 30, 1899. For example, in the Immediate code window I just typed

?now; cdbl(now)
and got the result

10/7/2019 11:58:12 PM 43745.99875

Your night shift shouldn't really start at 10:00 PM; the night shift for tonight started at #10/8/2019 22:00:00#, using the # date delimiter rather than the " string delimiter.

I'd have a table of Shifts with the starting and ending time, stored as a Date/Time value not a string, including the date. This will make it easier to handle weekends and holidays, in addition to solving the problem of calculating times over midnight.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Dale_j1992
post Oct 8 2019, 09:34 AM
Post#7



Posts: 26
Joined: 24-September 16



Afternoon All,

Many thanks for the responses.

Because i am using the >date() function i think i need some IF function but im not sure how to type it into the criteria.

As an example, night shift tonight would start 08/10/19 @ 22:00 and end 09/10/19 @ 06:00.

The issue i have is when it passes midnight the date changes, so before midnight it works but after it doesn't.

Is it possible to right some criteria, If the NOW() time on the computer is between 22:00:00 - 23:59:59 then Between >Date() 22:00:00 And >Date() +1 06:00:00.
and then after midnight it is between >Date() - 1 22:00:00 And >Date() 06:00:00.

I am trying to display information for the current shift as it happens, i hope what ive said makes sense, im just not that savvy with laying out the code correctly.

Kind regards



Go to the top of the page
 
projecttoday
post Oct 8 2019, 10:40 AM
Post#8


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


As long as you use Date + Time consistently you shouldn't have a problem. Date() is not Date + Time. It is Date only. 06:00:00 is not Date + Time. It is Time only.

--------------------
Robert Crouser
Go to the top of the page
 
Dale_j1992
post Oct 10 2019, 04:54 AM
Post#9



Posts: 26
Joined: 24-September 16



Morning,

Would you be able to give me some pointers, everything ive tried even with date/time together have issues passing midnight when using the NOW() or Date().

Because its live information i need to use the Now or Date function which works but when the date passes midnight it looks in the wrong day and returns no info.

For instance parts are getting scanned and the query is counting, time passes midnight the now() or Date() function then starts looking to 22:00:00 that night instead of the previous day, therefor returning 0 as the shift has not happened.

tonight for example 10/10/2019 - Pre midnight it would be looking at this 10/10/2019 22:00:00 - 11/10/2019 06:00:00, then after midnight it would look at this 11/10/2019 22:00:00 - 12/10/2019 06:00:00.

I hope this makes sense, im not the most savvy on access so i apologise if im being stupid here and missing something obvious.

Kind regards
Go to the top of the page
 
nuclear_nick
post Oct 10 2019, 05:45 AM
Post#10



Posts: 1,789
Joined: 5-February 06
From: Ohio, USA


I'm a little confused...

Are you talking about putting the information INTO or getting the information OUT OF the database? I thought you were trying to get information out, which means using Now() seems an odd way to go when getting historical information.

Now you're talking about live information, and real time stuff... so it kinda read like you were talking about getting information into the database as well.

The way we did that was a date field containing the 'effective date', so that when appending information to the table there were two fields... actual date and time, and the effective date, so that any time past midnight until that shift ended would get the 'effective date' of date()-1.

Then, to get the information back out of the database, you could query the 'effective dates' as a range, as needed.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
projecttoday
post Oct 10 2019, 06:37 AM
Post#11


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


QUOTE
Pre midnight it would be looking at this 10/10/2019 22:00:00 - 11/10/2019 06:00:00

QUOTE
after midnight it would look at this 11/10/2019 22:00:00 - 12/10/2019 06:00:00.

This doesn't make any sense to me. Somebody correct me if I'm wrong but pre-midnight is 10/10/2019 22:00:00 to 10/10/2019 23:59:59. After midnight is 11/10/2019 00:00:00 to 11/10/2019 06:00:00. (Note that 11/10 is October 11th not November 10th.) And 10/10/2019 22:00:00 to 11/10/2019 06:00:00 is a valid date range which should get you the all the data within that time period.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 08:53 AM