Formatting values for time (1 Viewer)

[waxdart23]

I am trying to add a range of time durations on a form but when the total value of hours is larger than 24 it goes back to zero.

eg If the total amount of hours is 40hours 30minutes then the total shows as 16:30.

Is there a formatting I can use so that Access recognises that a time is a value rather an actual time of day, rather than the standard "Short Time", "Medium Time" and "Long Time".

 

[Elana]

Check out "Format Property - Date/Time data type" in Access help.

For example - to show the hour of the current time, place this in the control source of your field

=Format(Time(),"hh")

You can play with adding and subtracting from there.

HTH

 

[Mile-O]

I have this function which I wrote when I had a problem although I was able to return the value as a string as it was only going on display in a form. You might find it useful as there is code in there to help split your time down to days, hours, minutes, and seconds.

Function TimeConversion(ByVal dteTime As Date) As String

    On Error GoTo TimeConversion

    ' Author: Mile-O-Phile
    ' Discussion: This function is designed to take a sum of times, which Access would return as a
    '             24-Hour clock value i.e when 23:59:59 has two seconds pass by, rather than the result
    '             be 24:00:01, Access would return 00:00:01. This function remedies this and returns the
    '             correct result as a string which can be used for display purposes only.

    ' Input(s): dteTime - the sum of time values that the user wishes to convert
    ' Process: change the given time from the constraints of the 24-Hour clock
    '          fix minute and second values that may contain single number values ie. 0-9
    '          eliminate any spaces that may form from string conversion
    ' Output(s): TimeConversion- the given time translated beyond the 24-Hour clock i.e 179:34:07

    Dim lngDays As Long, lngHours As Long, lngMinutes As Long, lngSeconds As Long
    Dim intCounter As Integer, strTemp As String
    
    ' using the 'Csng' function, convert the given time to broken down values
    lngDays = Int(CSng(dteTime))
    lngDays = lngDays * 24 ' turn number of days to hours
    lngHours = Int(CSng(dteTime * 24))
    lngMinutes = Int(CSng(dteTime * 1440))
    lngSeconds = Int(CSng(dteTime * 86400))
    lngHours = lngDays + (lngHours Mod 24) ' calculate total of hours
    lngMinutes = lngMinutes Mod 60 ' get actual minutes
    lngSeconds = lngSeconds Mod 60 ' get actual seconds
    
    ' fix single figure values for minutes, i.e change :5 to :05
    Select Case lngMinutes
        Case Is = 0
            strTemp = Str(lngHours) & ":00"
        Case Is < 10
            strTemp = Str(lngHours) & ":0" & lngMinutes
        Case Else
            strTemp = Str(lngHours) & ":" & Str(lngMinutes)
    End Select
    
    ' fix single figure values for seconds, i.e change :5 to :05
    Select Case lngSeconds
        Case Is = 0
            strTemp = strTemp & ":00"
        Case Is < 10
            strTemp = strTemp & ":0" & Str(lngSeconds)
        Case Else
            strTemp = strTemp & ":" & Str(lngSeconds)
    End Select
    
    ' the 'Str()' function may append spaces to the newly formed string, this loop eliminates these
    For intCounter = 1 To Len(strTemp)
        If Mid(strTemp, intCounter, 1) = " " Then
            ' do nothing
        Else
            TimeConversion = TimeConversion & Mid(strTemp, intCounter, 1)
        End If
    Next intCounter
    
Exit_TimeConversion:
    Exit Function

Err_TimeConversion:
    MsgBox Err.Number & Err.Description
    Resume Exit_TimeConversion
    
End Function

On looking at it now I suppose I could break it down further with a smaller function; but that's another day.

 

[Agism]

I have a database that adds the number of hours an employee was logged in the phone system and how long he was on available, etc.

What I did was use queries to convert all the times to minutes, add the times I needed and convert back to hr:mm format. It was some what tedious, but it works.

 

[waxdart23]

Thanks Mile-O-Phile, your function worked a treat.