Hi There,

I'm trying to put together a database to record swimming times in, and I am having problems. The time needs to be mm:ss:hh, (minutes, seconds, hundreths of a second - with minutes and seconds going up to 60 and hundreths up to 99). I can get it to work in excel by setting a custom format of mm:ss:00, but when I try this in Access it sets it to nn:ss".00". A time of 01:25:10 then displays as 25:10.00 !!

I can't see any way to do it and to make matters worse I need to do calculations later on to find out average time, best time etc.

Can anyone help, before my laptop gets chucked through the window ??

Use a long integer field and store your elapsed time as seconds. Then to display the time in hh:mm:ss format, use the following function.

Public Function FormatTime(ElapsedTime As Long) As String
Dim vHours as Long
Dim vMinutes as Long
Dim vSeconds As Long

vHours = ElapsedTime \ 3660
vMinutes = (ElapsedTime - (vHours * 3660)) \ 60
vSeconds = ElapsedTime - (vHours * 3660) - (vMinutes * 60)

FormatTime = vHours & ":" & vMinutes & ":" & vSeconds
End Function

Hi Pat,

Thanks for your response. Unfortunately, being new to all of this malarky, I dont really understand what you mean. If I set the value as an integer, then the users (swimming coaches) won't be able to insert the time in the format they understand (01:25:78 for example). Is there any way I can set the format so they enter it as above and it stays that way and I can calculate on it ?

The Date/Time data type is a specific internal format in Access. So any format that you specify for a field will NOT have any impact on how data is actually stored. If you needed hours/minutes/seconds, there are ways to get the date/time data type to accomodate an elapsed time as long as it does not exceed 24 hours. But with the last element being miliseconds, you'll need to handle the problem with coding.

I would define three columns - ElapsedMin, ElapsedSec, ElapsedMilliSec. You can place them close together on the form with colons between the fields so to the user it looks like a single field. Internally, you'll need to store the elapsed time as miliseconds. Place the code to convert to milliseconds in the BeforeUpdate event of the form so you can store the elapsed time as milliseconds. Then place code to convert from milliseconds to your desired display value in the onCurrent event of the form. Make sure that you check for a new record so you can bypass the conversion code when you are adding a new record.

You'll need to modify the function I posted to change the divisors.