Time in table

[Zippyfrog]

I am trying to setup a database for some track and cross country runners in order to track personal stats, however, I am running into an issue. I want to make the database I have more efficient, but the issue I am having is how to setup the final time field. Currently in my database is setup with a FinalTimeMinutes field, and a FinalTimeSeconds field, and on the reports, I concatenate the two fields together and put a colon in between. I also have a field that lists the distance so they can type in the distance and find average mile pace.

Is there any way that I can setup a single field Called "FinalTime" where the user would type in "30:25" if they ran for 30 minutes and 25 minutes and have it still be able to divide by the distance. Currently for the formula for average, I take the final time minutes multiply by 60, add the finaltimeseconds field, divide by the distance to find average seconds per mile, then convert that to average minutes/mile. I tried Date/Time but get errors when I have larger times than a 24 hour day.

Is the way I set it up really the best way to do it, or is there a more simple way?

 

[gemma-the-husky]

if they ran 4.6 miles say in 30:25

just divide 30:25/4.6 and show the result as another time format

this will work out minutes/secs

---------
just tried it - you need to enter the hours as in this example

Sub trytime()
MsgBox (Format(TimeValue("0:30:25") / 4.6, "long time"))
End Sub

--------
if you enter 30:25 into a time field it MIGHT be taking it as 30hrs 25mins

 

[neileg]

Working with date/time fields can get a bit messy when you just want times. I tend to store numbers of minutes or seconds when I want elapsed times.

 

[Zippyfrog]

Working with date/time fields can get a bit messy when you just want times. I tend to store numbers of minutes or seconds when I want elapsed times.

So do you use a setup like I do with a separate field for minutes and seconds?

 

[neileg]

No. I might use separate controls on the form to capture user input, but I would store the time as either minutes or seconds depending on the level of precision needed.