I'd suggest storing the active and idle times each as three separate columns
(fields) of integer number data type, ActiveHours, ActiveMinutes,
ActiveSeconds, IdleHours, IdleMinutes and IdleSeconds. Make each a
'Required' column in table design to prohibit Nulls and give each a
DefaultValue property of zero. For data entry just group the controls bound
to each set of three fields together side by side so you can tab from one to
the next. You can then return the total time as a string by passing the
values of the six columns into the following function, which first converts
the times to seconds then adds them and then converts them to a string of
hours, minutes and seconds:
Public Function TotalTime(ActiveHours As Integer, _
ActiveMinutes As Integer, _
ActiveSeconds As Integer, _
IdleHours As Integer, _
IdleMinutes As Integer, _
IdleSeconds As Integer) As String
Dim TotalActiveMinutes As Long
Dim TotalActiveSeconds As Long
Dim TotalIdleMinutes As Long
Dim TotalIdleSeconds As Long
Dim TotalHours As Long
Dim TotalMinutes As Long
Dim TotalSeconds As Long
TotalActiveMinutes = (ActiveHours * 60) + ActiveMinutes
TotalIdleMinutes = (IdleHours * 60) + IdleMinutes
TotalActiveSeconds = (TotalActiveMinutes * 60) + ActiveSeconds
TotalIdleSeconds = (TotalIdleMinutes * 60) + IdleSeconds
TotalSeconds = TotalActiveSeconds + TotalIdleSeconds
TotalHours = TotalSeconds \ 3600
TotalMinutes = (TotalSeconds - (TotalHours * 3600)) \ 60
TotalSeconds = (TotalSeconds - (TotalHours * 3600)) - (TotalMinutes * 60)
TotalTime = TotalHours & ":" & _
Format(TotalMinutes, "00") & ":" & _
Format(TotalSeconds, "00")
End Function
Paste the function into a standard module in the database and call it in a
query, or a computed control in a form or report:
TotalTime([ActiveHours], [ActiveMinutes], [ActiveSeconds], [IdleHours],
[IdleMinutes], [IdleSeconds])
Ken Sheridan
Stafford, England
