Night shift data query

[hudaz]

Hi All,

I've got a problem with a query i am working on at the minute and i can't seem to find the answer.

I am creating a query that looks at data collected from staff working the night shift. They start at 6pm every day and finish at 6am the following day. I'm looking for an expression that will allow me to show the current shift's data.

I've played around with the date() function but i'm a novice and can't seem to get anything that will show just the specific shift data i require.

Hope one of you geniuses may be able to help me with the correct function.

Thanks!

Andy

 

[Uncle Gizmo]

The problem of shift work date and time crops up on this forum quite often. So my advice would be to search the forum and read up on how other people have tackled the problem.

The essence of the problem is that part of your shift is in one day and part of the shift is in another day. The shift passes through midnight from one day to another.

 

[hudaz]

Hi,

First off thanks for the reply, it's appreciated.

I've tried searching the forums but i can't find anything specific to what i'm trying to do, Do you have a thread in mind that is similar to the problems i face ?

Thanks!

 

[arnelgp]

untested.

select id, empname, timein, timeout where timein >= DateValue(timein)+TimeValue("6:00 pm") And timeout <= DateValue(DateAdd("d",1,timein))+TimeValue("6:00 am") from timecard;

 

[Uncle Gizmo]

Midnight Time calculation Help....

Record Sort using Time/Date, that crosses midnight

More Google Search results HERE:-

 

[jdraw]

Why not record the Now() for the punchIn and punchOut events?

eg. simulate punchIn and punchOut times that cross midnight boundary

Sub punch()
'
'Recording date and time of PunchIn and Date and Time of punchOut.
'Showing the time difference in (1) Hours and (2) Hours and Minutes
'
'Sample with simulated PunchIn and PunchOut times
'
    Dim punchIn As Date: punchIn = #3/21/2016 6:01:15 PM#     'Date/time at punchIn
    Dim punchOut As Date: punchOut = #3/22/2016 5:59:58 AM#   'Date/time at punchOut
    Debug.Print "1 Time between punches in hours " & Round(DateDiff("n", punchIn, punchOut) / 60, 2)
    Debug.Print "2 Time between punches is  " & DateDiff("n", punchIn, punchOut) \ 60 & "  hours  and " _
                & (DateDiff("n", punchIn, punchOut) / 60 - DateDiff("n", punchIn, punchOut) \ 60) * 60 & "  minutes"
End Sub

Results:

1 Time between punches in hours 11.97
2 Time between punches is 11 hours and 58 minutes