Solved I need guidance to calculate Duty hours and Overtime hours (1 Viewer)

[Khurramshaikh47]

you need to Copy the module from my latest demo db.
is BTID unique? if not select a unique key to use in the query.

Yes BTID is unique. It can not be duplicated.

 

[Khurramshaikh47]

I tried by creating a new simple DB and importing your module in it. And then tried the fnComputeTime function. Please See the Screenshot

you need to Copy the module from my latest demo db.
is BTID unique? if not select a unique key to use in the query.

When this function is applied, it returns the perfect output with the current (Time In & Time Out) values once. Then when I am changing TimeIn and TimeOut the output is not changing. Please See the Screenshot

Then I copied all the components of my existing database like tables, queries, forms, and reports to your given dummy database. And then tried this (fnComputeTime) function.

Result is same:

Hope for your response.

Thank you again for your precious time.

 

[arnelgp]

you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If

 

[Khurramshaikh47]

you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If

Commented Out

An Error occurred when I am trying to use this function in query now:

Error In VB when compiling:

Please check the code:

'arnelgp
Public Function fnComputeTime(ID As Variant, ByVal t_in As Variant, t_out As Variant, ReturnWhat As String) As Double
' parameters:
'
' id            the pk field value
' t_in          time in
' t_out         time out
' ReturnWhat    which value will the function return.
'               either "reg" (regular time/duty time) or "ot" (overtime hour)
'
Static this_id As Variant
Static regular_time As Double, over_time As Double
Dim tm As Variant, t1 As Variant, t2 As Variant, tfOT As Boolean
If IsNull(ID) Or IsDate(t_in) = False Or IsDate(t_out) = False Or InStr(1, "/reg/ot/", ReturnWhat) = 0 Then
    Exit Function
End If
'If ID <> this_id Then
'    regular_time = 0: over_time = 0
'    this_id = ID
'    t1 = t_in
'    t2 = t_out
'    ' compute regular time
'    If t1 <= #9:15:00 AM# Then
'        t1 = #9:00:00 AM#
'    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function

 

[monheimx9]

The last End If isn't needed

 

[Khurramshaikh47]

The last End If isn't needed

Deleted

 

[Khurramshaikh47]

you comment out this Second If:

'If ID <> this_id Then
...
...
...
'End If

I commented
If and End If and leave the content between them.

And Deleted the last End If before over_time = Round(tm, 2)
Its working now.

Thank Yo So much to all specially Mr. Arnel (A greate Teacher).

 

[arnelgp]

i mean this code:

'arnelgp
Public Function fnComputeTime(ID As Variant, ByVal t_in As Variant, t_out As Variant, ReturnWhat As String) As Double
' parameters:
'
' id            the pk field value
' t_in          time in
' t_out         time out
' ReturnWhat    which value will the function return.
'               either "reg" (regular time/duty time) or "ot" (overtime hour)
'
Static this_id As Variant
Static regular_time As Double, over_time As Double
Dim tm As Variant, t1 As Variant, t2 As Variant, tfOT As Boolean
If IsNull(ID) Or IsDate(t_in) = False Or IsDate(t_out) = False Or InStr(1, "/reg/ot/", ReturnWhat) = 0 Then
    Exit Function
End If
'If ID <> this_id Then
    regular_time = 0: over_time = 0
    this_id = ID
    t1 = t_in
    t2 = t_out
    ' compute regular time
    If t1 <= #9:15:00 AM# Then
        t1 = #9:00:00 AM#
    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
'End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function

 

[Khurramshaikh47]

i mean this code:

'arnelgp
Public Function fnComputeTime(ID As Variant, ByVal t_in As Variant, t_out As Variant, ReturnWhat As String) As Double
' parameters:
'
' id            the pk field value
' t_in          time in
' t_out         time out
' ReturnWhat    which value will the function return.
'               either "reg" (regular time/duty time) or "ot" (overtime hour)
'
Static this_id As Variant
Static regular_time As Double, over_time As Double
Dim tm As Variant, t1 As Variant, t2 As Variant, tfOT As Boolean
If IsNull(ID) Or IsDate(t_in) = False Or IsDate(t_out) = False Or InStr(1, "/reg/ot/", ReturnWhat) = 0 Then
    Exit Function
End If
'If ID <> this_id Then
    regular_time = 0: over_time = 0
    this_id = ID
    t1 = t_in
    t2 = t_out
    ' compute regular time
    If t1 <= #9:15:00 AM# Then
        t1 = #9:00:00 AM#
    End If
    If (t2 >= #12:00:00 AM# And t2 <= #8:59:59 AM#) Or _
        (t2 >= #5:45:00 PM#) Then
        t2 = #6:00:00 PM#
    End If
    tm = DateDiff("n", t1, t2)
    tm = tm / 60
    regular_time = Round(tm, 2)
    ' compute overtime
    t2 = t_out
    tm = 0
    tfOT = True
    Select Case True
    Case (t2 = #12:00:00 AM#)
        t1 = #1:00:00 PM#
        t2 = #9:00:00 PM#
    Case (t2 > #12:00:00 AM# And t2 <= #8:59:59 AM#)
        tm = 480
        t1 = #12:00:00 AM#
    Case (t2 >= #5:45:00 PM# And t2 <= #6:30:00 PM#)
        tfOT = False
    Case (t2 >= #6:00:00 PM# And t2 <= #11:59:59 PM#)
        t1 = #6:00:00 PM#
    Case Else
        tfOT = False
    End Select
    If tfOT Then
        tm = tm + DateDiff("n", t1, t2)
        tm = tm / 60
    End If
    over_time = Round(tm, 2)
'End If
fnComputeTime = IIf(ReturnWhat = "reg", regular_time, over_time)
End Function

Thank you so much, Mr. Arnel