Time calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a database I record the time I perform on certain activities regularly. I
use a mask as 00:00:00 (hour:minutes:seconds). Now I want to sum these data
to an aggregate sum in a control on the form I use for input. The field that
holds the data is named "Time". How do I write that function. The form is
based on a query.
 
In a database I record the time I perform on certain activities
regularly. I use a mask as 00:00:00 (hour:minutes:seconds). Now I
want to sum these data to an aggregate sum in a control on the
form I use for input. The field that holds the data is named
"Time". How do I write that function. The form is based on a
query.
Click to expand...

What type of field is time? is it a string or is it a date/time type?

In general you have to convert everything to seconds, sum those and
then reformat the resulting value back to ddd:hh:mm:ss

Use Google Groups to search for a function called Sec2Dur that does
that, I posted it about 2 years ago.
 
Bob Quintal said:
What type of field is time? is it a string or is it a date/time type?

In general you have to convert everything to seconds, sum those and
then reformat the resulting value back to ddd:hh:mm:ss

Use Google Groups to search for a function called Sec2Dur that does
that, I posted it about 2 years ago.
Click to expand...

In addition to what Bob's suggested, consider renaming your field.

Time is a reserved word, and using it for your own purposes can lead to
problems. For a good discussion on what names to avoid in Access, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html
 
I could not find your function at Google groups and I still don't know how to
write that function. Thanks anyway!
 
I could not find your function at Google groups and I still don't
know how to write that function. Thanks anyway!
Click to expand...

Didn't take me long. Here it is again. Originally poster march 15,
2004 in a thread titled hours in excess of 24

Feel free to modify it to suit your data.

Public Function sec2dur(seconds As Long) As String
On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":" &
Format(secs, "00")

End Function
 
Back
Top