Duration Cut off

[corbitext]

Hi to all,

I have a table of daily_outs with two columns date_out and date_in and below are my sample:
Table:daily_outs
line________date_out______________date_in____________ duration(minutes)
1______#12/25/2006 12:26 PM#___#12/27/2006 13:26 PM#_____2,940.03
2______#12/26/2006 04:33 AM#___#12/27/2006 12:26 PM#_____1,913.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/25/2006 01:05 AM#___#12/28/2006 15:27 PM#_____5,182.22

I would like to query the date_out=12/26/2006 00:00 AM and date_in=12/26/2006 11:59 PM. Below are the result I would like.

line________date_out______________date_in____________ duration(minutes)
1______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98
2______#12/26/2006 04:33 AM#___#12/26/2006 23:59 PM#_____1,166.00
3______#12/26/2006 01:16 AM#___#12/26/2006 15:25 PM#______849.03
4______#12/26/2006 00:00 AM#___#12/26/2006 23:59 PM#_____1,439.98

Can anyone help me on this?

Thanks,
Corbitext

 

[neileg]

It's not at all clear what the caculation is. I can't see how the result is related to the original data.

 

[corbitext]

Sir Neileg,

I would like to query only the date of 12/26/2006 which will count only the duration of that day and exempt those are not within their range of that day.

 

[Jon K]

Type/Paste this function in a module.

Public Function getMinutes(dDate, dStart, dEnd) As Double
    Dim CStart As Date
    Dim CEnd As Date
    
    CStart = dDate
    CEnd = dDate + #11:59:59 PM#
    
    If dStart <= CEnd And dEnd >= CStart Then
       CStart = IIf(CStart > dStart, CStart, dStart)
       CEnd = IIf(CEnd < dEnd, CEnd, dEnd)
       getMinutes = Round((CEnd - CStart) * 24 * 60, 2)
    End If

End Function

Then use the function in a query like this:-
Duration in Minutes: getMinutes(#12/26/2006#, [Date_Out], [Date_in])


If you want to have exactly 1440 minutes in a day rather than 1439.98 minutes, you can change the line:-
CEnd = dDate + #11:59:59 PM#

to:-
CEnd = dDate + 1
.

 

[corbitext]

Thanks a lot, Jon K

 

[corbitext]

Sir Jon K

How about if I change the date of #12/26/2006# and range to one month ie. from 5/26/2007 to 6/25/2007.

th

 

[Jon K]

The basic principle is the same. You just have to pass the starting date and ending date to the function and set the proper CStart and CEnd in the code.

Public Function getMinutes2([b]dDateStart, dDateEnd,[/b] dStart, dEnd) As Double
    Dim CStart As Date
    Dim CEnd As Date
    
    [b]CStart = dDateStart
    CEnd = dDateEnd + 1[/b]
    
    If dStart <= CEnd And dEnd >= CStart Then
       CStart = IIf(CStart > dStart, CStart, dStart)
       CEnd = IIf(CEnd < dEnd, CEnd, dEnd)
       getMinutes2 = Round((CEnd - CStart) * 24 * 60, 2)
    End If

End Function

In the query:-
getMinutes2(#5/26/2007#, #6/25/2007#, [Date_Out], [Date_in])
.

 

[Niroth]

Jon K,

Just need a bit of help here. I have about the same problem. I need to divide my work into two shifts, one from 7am to 6pm and another from 6pm to 7am. Can I use the same thing to filter for records that fall within those two periods of the day in seperate queries? Is there anyway I can change it so the date becomes dynamic so I don't have to enter the date everyday for my daily reports?

Thanks in advance, any help would be really appreciated.

 

[Jon K]

Niroth,

See the function and query in the attached sample database.
You have one shift of 11 hours (7am-6pm) and another of 13 hours (6pm-7am).

The sample database assumes date and time are stored in the same field as in the OP's sample data.

Using clock-in clock-out times in code to filter a shift can be quite complicated as there are different ways of logging data -- date & time in one field; date and time in separate fields; clock-out date not logged. And there may also be early-comers, late-comers, early-leavers and late-leavers to consider.
.