Date Difference VBA Code

[MHamid]

Hello,

I have the below function to calculate the difference between two times (when the user LogsIn and LogsOut). I want to incorporate if Idle time was used to close the program, then deduct 15 minutes. If not, then do nothing.
Is this possible?

Function DisplayTime(start As Date, finish As Date, fmt As String)
Dim Hours As Long, Minutes As Long, totalSeconds As Long, SecondLeft As Long
'format the differece of 2 dates or time
totalSeconds = DateDiff("s", start, finish)
Hours = Int(totalSeconds / 3600)
SecondLeft = totalSeconds Mod 3600
Minutes = Int(SecondLeft / 60)
Select Case fmt
    ' format = 100 Hours 50 Minutes
    Case "H M"
    If Hours = 0 Then
    DisplayTime = Minutes & IIf(Minutes <> 1, " Minutes", " Minute")
    Else
    DisplayTime = Hours & IIf(Hours <> 1, " Hours", " Hour") & Minutes & IIf(Minutes <> 1, " Minutes", " Minute")
    End If
End Select
End Function

Let me know if you need the idle time code as well.

Thank you

 

[xenou]

If idle time is used then just subtract the extra minutes you want to subtract. You just need to know if to subtract or not - that can be a parameter in your function or something you determine in the body of the function.

 

[MHamid]

Hello,

I'm sorry, I'm trying to wrap my head around how to go about doing this when the idle time function is used in one form and the DisplayTime function is being used in another form. I will only need to deduct 15 minutes from the DisplayTime in the Report_rptLogInTime when IdleTime was used.

I'm thinking that the minus 15 minutes if IdleTime was triggered should be used in the Report_rptLogInTime since that is the report that will be calculating how long a user has been using the Data Entry Form. Current in the Report_rptLogInTime there is a textbox labeled "Text27" with a formula in the control source (on the property sheet) as =DisplayTime([LogInTime],[LogOutTime],"H M").

But I'm also thinking that based on your comment, I would need to incorporate parts of the IdleTime function as a parameter within the DisplayTime function. Am I misunderstanding?


Thank you

 

[xenou]

Not sure I can answer your question. Basically how do you know if idle time is used. Whatever makes that work, you would incorporate it into your calculation (either when it happens or at some point later on as an adjustment).

My own take is probably oversimplified, but I imagine it this way:

1) An idle time trigger fires (i.e., database has been idle for 15 minutes).
2) you immediately record the the current user login time and close forms. Since this is an idle time event you also subtract 15 minutes from the users login time.

The technicalities of how you do this are up to you as a programmer. I would probably use a parameter in my function/query/form that records the time (subtractIdleTime -- yes/no or IdleTimeAdjustment -- time in minutes). You can do it any way you like though.

To be honest I don't really know if you are just displaying some value or storing some value - if it's just a matter of display, however, its more or less the same thing going on.