Duration of time

[Sean]

I'm creating a database to keep track of all my running races.

I have a form setup for each race distance. What I need to be able to do is
put in the length of time the race took. For example

3:32:14:31 (meaning 3 hours, 32 minutes, 14 seconds, 31 milliseconds)

I then need it to calculate my avg pace. So for a 10K race if I ran
0:55:14:31 it would tell me my average pace was in minuets and seconds per/KM.

You would think Access would have function for duration of time but it
doesn't seem too and since my code writting capablilities are really horrible
I'm a little lost.

Any help would be appreciated. Thanks.

 

[Imran J Khan]

Access can handle time. In the table, create a new field and set the data
type as date/time; format as long time. In the form, create a textbox, set
control source to new field. When calculating the avg, you should be able to
use the average function in the QBE.

Hope that helps

Imran J Khan

 

[John Spencer]

Duration of time should be stored in a number field. The duration should be in
one specific unit of time. Hours or minutes or seconds or milliseconds.

You can store the number of seconds in a field that is Currency - which will
accurately store the decimal portion (milliseconds) up to 4 digits.

So for 3:32:14:31 you would store 12734.031
Which is 3*60*60 + 32*60 + 13 + 31/1000

You could write VBA functions to generate the number to store and to generate
a string value to display.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sean]

That would work if I was looking for the time of day but I'm looking for the
length of time my race took me.

Thanks though.

 

[Sean]

Wow, might be a bit over my head. I guess I'll just make it so I can put
everything in manually instead of it calculating the avg out for me.

thanks any ways.

 

[John Spencer]

Two untested AIR CODE VBA functions that may work for you

Public Function storeTime (strIN)
'Expects a string in format of
' hhh:mm:ss:xx
Dim vaTime as Variant
If Len(strIn & "") = 0 Then
storeTime = 0 'or Null
else
vaTime = Split(strIn,":")
storeTime = VaTime(0)*3600 +vaTime(1)*60 + vaTime(2) + vaTime(3)/1000
End IF
End Function

Public Function ShowTime(numIn)
If Len(numIN & "") = 0 Then
ShowTime = "00:00:00:00"
Else
ShowTime = Int(NumIN) \3600 & _
":" & Int(NumIN) / 60 mod 60 & _
":" & Int(NumIN) mod 60 & _
":" & (Numin - int(Numin)) * 1000
End If
End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Access can handle time. In the table, create a new field and set the data
type as date/time; format as long time. In the form, create a textbox, set
control source to new field. When calculating the avg, you should be able to
use the average function in the QBE.

You're only partially correct, Imran. Date/Time fields are limited to 1 second
as the smallest unit, and he needs milliseconds; and they're really not
suitable for durations, but are better for specific points in time.