Access Coding

[Eranka]

Hi Everyone

I am kinda new to access coding. Appreciate if you could help me on below problem



I have attached a picture in here. I am calculating the time difference between time_in and Time_out and displaying the difference in Actual_hours field.
On the next field "Accepted_Hours" filed the "Actual_hours" data should appear and if a time exceed more than 4 hours it should automatically change to 4. How to do this part?

Ex:

Time_In	Time_Out	Actual_Hours	Accepeted_Hours
6:21:00 PM	9:00:00 PM	2:39	2:39	This is fine
1:07:00 AM	6:00:00AM	4:53	4	4.53 to 4

[Minty]

In a query you can use an IIf() as a calculated field;

AcceptedHours: IIf([ActualHours] > 4 , 4 , [ActualHours])

If Actual Hours is a calculated field itself (and it should be), then you can't refer to that in the same query, you would need to perform the calculation again within the IIf() statement.

[Eranka]

Hi

I tried like

=IIf(([Time_Out]-[Time_In])>4,4,([Time_Out]-[Time_In]))

But it gives out put as below in the accepted hours. The 4.53 does not change as 4 automatically.

Time_In

Time_Out

Actual_Hours

Accepeted_Hours

6:21:00 PM

9:00:00 PM

2:39

2:39

1:07:00 AM

6:00:00AM

4:53

4.53

[Minty]

Can you make a field to display [Time_out]-[Time_In] to see what results you are getting ?

[Eranka]

[Time_out]-[Time_In] = [Actual_Hours] <- this is the filed that i have created.

I just want to display the if the [Actual_Hours] is > 4 then as 4.

[Minty]

Sorry I'm being thick - Don't format the output as a time, the value isn't 4, it will be a very small number as date time are stored as decimal numbers so now is actually (Do this in the immediate window)
? CDec(now()) 43090.4575347222

The integer part is the Day , the fractional part is the Time.

So display CDec([Time_out]-[Time_In]) to see what values you are getting.

Edit : 4 hours is 0.166666666664241 so check for that value.

[Eranka]

Hi

How did you get 4 = 0.166666666664241 value?

[Minty]

I used the immediate window of the VBA editor and typed in

?CDec( Date() - (Date() + TimeValue("4:00")))

So to explain what that breaks down as
? means Print , Date() is today at 00:00:00 and therefore Date + TimeValue("4:00") is today at 04:00:00 hence four hours.

[June7]

As Minty said, dates and times are actually stored as decimal numbers. What you see is a default formatting. Calculating the difference of those time values results in a decimal figure which is a portion of a 24-hour day. To get the result in hours multiply by 24.

([Time_out]-[Time_In]) * 24

However, it is not necessary to repeat the calc. Example:

SELECT Time_In, Time_out, ([Time_out]-[Time_In]) * 24 AS ActualHours, IIf([ActualHours]>4,4,[ActualHours]) AS Accepted FROM tablename;

If the time span crosses midnight, this will not work because need the date part to properly calc elapsed time.

[Eranka]

Hi All

Can you help to combine these two codes.

IIf(([Session_ID]="Evening") And (([Time_Out]-[Time_In])*24)>=3,0.125,(([Time_Out]-[Time_In])))

IIf((([Time_Out]-[Time_In])*24)>=4,0.166666666,(([Time_Out]-[Time_In])))

[ssanfu]

Maybe try this: (not sure if I have the parentheses correct)

IIF(([Session_ID]="Evening", IIF((([Time_Out]-[Time_In])*24)>=4, 0.166666666, IIF((([Time_Out]-[Time_In])*24)>=3,0.125,([Time_Out]-[Time_In]))),0)

-----------------------------------------------------------------------------------------------------------------------------

What I did to get this formula:

So you have 3 conditions:
1) [Session_ID]="Evening",
2) (([Time_Out]-[Time_In])*24)>=4
3) (([Time_Out]-[Time_In])*24)>=3

Now create the formula
First step:
1st condition syntax: IIF(condition, True, False). If [Session_ID] is not equal to "Evening"), return zero.
IIF([Session_ID]="Evening", True, 0)


Second step:
Then add the 2nd condition in the TRUE part
IIF([Session_ID]="Evening", IIF(condition, True, False), 0)

Add the 2nd condition and the TRUE return value
IIF([Session_ID]="Evening", IIF((([Time_Out]-[Time_In])*24)>=4, 0.166666666, False), 0)


Third step:
Since it wasn't >=4, add the next condition
IIF([Session_ID]="Evening", IIF((([Time_Out]-[Time_In])*24)>=4, 0.166666666, IIF((([Time_Out]-[Time_In])*24)>=3, 0.125, False)), 0)

Fourth step:
Then the FALSE return value

IIF([Session_ID]="Evening", IIF((([Time_Out]-[Time_In])*24)>=4, 0.166666666, IIF((([Time_Out]-[Time_In])*24)>=3, 0.125, ([Time_Out]-[Time_In]))), 0)

So I start out with
IIF(condition,TRUE,FALSE) <--first test
then add another test
IIF(condition, IIF(condition,TRUE,FALSE),FALSE) <--second test
then add the third test
IIF(condition, IIF(condition,TRUE, IIF(condition,TRUE,FALSE)),FALSE) <--third (final) test





Simple...No??

[Eranka]

Hi

Thank you for the above solution.

IIf(([Session_ID]="Evening") And (([Time_Out]-[Time_In])*24)>=3,0.125,IIf((([Time_Out]-[Time_In])*24)>=4,0.166666666,IIf(([Level_Of_Unit]="F0") And (([Time_Out]-[Time_In])*24)>=6,0.25,([Time_Out]-[Time_In]))))

I tried the above code with slight modification, but its not working. Could you help me?

[Minty]

I'd write this into a function. Once you get to 3 or more IIf's it becomes hard to see what's actually happening unless you colour code it like Steve has.
Also it makes changing any of the parameters really simple and clean in one place. Something like

Public Function fnMaxTimes(tInTime As Date, tOutTime As Date, sSession As String) As Date

    Dim TimeDiff         As Single
    Const c4hr           As Single = 0.166666666
    Const c3Hr           As Single = 0.125
    
    'Logic Rules
    '1) [Session_ID]="Evening", then rule 2 applies
    '2) (([Time_Out]-[Time_In])*24)>=4
    '3) (([Time_Out]-[Time_In])*24)>=3


    TimeDiff = tOutTime - tInTime
    
    fnMaxTimes = TimeDiff
    
    If TimeDiff > c4hr Then fnMaxTimes = c4hr
    
    If sSession = "Evening" Then
        If TimeDiff > c3Hr Then fnMaxTimes = c3Hr
    End If
  


End Function

[ssanfu]

I tried the above code with slight modification, but its not working. Could you help me?

What does "not working" mean?
Any Error messages?

Can you provide examples of what you have and what you want to see?

------------------------------------------------------------------------------------------------

Re-writing your formula in Post #12 to a standard IF() function, it looks like this:

    If ([Session_ID] = "Evening") And (([Time_Out] - [Time_In]) * 24) >= 3 Then
        x = 0.125
    Else
        If (([Time_Out] - [Time_In]) * 24) >= 4 Then
            x = 0.166666666
        Else
            If ([Level_Of_Unit] = "F0") And (([Time_Out] - [Time_In]) * 24) >= 6 Then
                x = 0.25
            Else
                x = [Time_Out] - [Time_In]
            End If
        End If
    End If



'-------- OR ----------
'could use ElseIF syntax

    If ([Session_ID] = "Evening") And (([Time_Out] - [Time_In]) * 24) >= 3 Then
        x = 0.125
    ElseIf (([Time_Out] - [Time_In]) * 24) >= 4 Then
        x = 0.166666666
    ElseIf ([Level_Of_Unit] = "F0") And (([Time_Out] - [Time_In]) * 24) >= 6 Then
        x = 0.25
    Else
        x = [Time_Out] - [Time_In]
    End If

You can paste this into a module, change the variables (in blue) and execute the sub to see the results (use F8 to step through the code or F5 to execute).
Prints the result to the immediate window

Public Sub test()
    Dim x As Single
    Dim dTime_In As Date
    Dim dTime_Out As Date
    Dim sSession_ID As String
    Dim sLevel_Of_Unit As String

    '------------------------------------------------------------
    'enter variables - change values to get different results
    dTime_In = #6:00:00 AM#
    dTime_Out = #11:00:00 AM#
    sSession_ID = "Evening"
    sLevel_Of_Unit = "F0"
    '------------------------------------------------------------

    If (sSession_ID = "Evening") And TimePart >= 3 Then
        Debug.Print "TimePart = " & TimePart & " return 0.125"
        '   x = 0.125
    Else
        If TimePart >= 4 Then
            Debug.Print "TimePart = " & TimePart & "; return 0.166666666"
            '   x = 0.166666666
        Else
            If (sLevel_Of_Unit = "F0") And TimePart >= 6 Then
                Debug.Print "TimePart = " & TimePart & "; return 0.25"
                '   x = 0.25
            Else
                Debug.Print "TimePart = " & TimePart & "; return " & Round((dTime_Out - dTime_In), 5)
                '   x = Round(dTime_Out - dTime_In, 5)
            End If
        End If
    End If

End Sub

[davegri]

ErankaDates-v1.zip
Here's another approach.
It uses a function to calculate the elapsed time between 2 dates in hours and minutes.
The example doesn't take into account the evening session, but could be easily added, I think.
Adapted code originally from Graham R Seach MCP MVP