Date/Time - Blood pressure rising !

[DanHughes]

Hi There,

I'm trying to put together a database (access 2000)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 ??

 

[MIKElaw]

Had to do something similar several years ago in A97 and never did find a way that might be described as elegant. So lateral thinking; create two fields, one for the HH:MM:SS (LongTime) and one as numeric to hold the fractional seconds. Put them together on the form and the visual side is OK BUT any calcs need to be done in VBA which I did by converting the LongTime to seconds and calculating and then converting it back but most of the ones I was working with were less that a second different anyway so only the 1/100ths showed

Not elegant but it worked then and the system is still running. Anyway no one can see what goes on behind the screen.

Hope this either A: works or B: sets your lateral trains going

Mike

 

[RickSpr]

I'm pretty sure you're not going to be able to store this in a Date-type variable. So first, you're going to have to figure out how you want to store it internally.

I would store it as a Long Integer in units of hundredths of a second. 1 second would be stored as 100, 1 minute would be 6000 (1 * 60 * 100), and 2:25:17 would be 14517 (((2 * 60 * 100) + 25 * 100) + 17). The maximum value of 59:59:99 would be 359999. Using a Long Integer makes it fairly easy to do your calculations.

Below is some code to help you with input and output of these values in a text box. You'll need to use an unbound text box in order to get it in the format you want, so you'll have to use event procedures to move the information between the form and the form's underlying record. Specifically, if your table field is called ElapsedTime and your text box is called txtElapsed, you need event procedures like the following:

    Dim TempTime As Long

    Private Sub Form_Current()
        If IsNull([ElapsedTime]) Then 
            txtElapsed = Null
        Else
            txtElapsed = TimeToStr([ElapsedTime])
        End If
    End Sub

    Private Sub txtElapsed_BeforeUpdate(Cancel As Integer)
        Dim t As Long, msg As String

        If Not IsNull(txtElapsed) Then
            If StrToTime(Trim(txtElapsed), t, msg) Then
                TempTime = t ' pass it to AfterUpdate
            Else
                MsgBox msg
                Cancel = True
            End If
        End If
    End Sub

    Private Sub txtElapsed_AfterUpdate()
        If IsNull(txtElapsed) Then
            [ElapsedTime] = Null
        Else
            [ElapsedTime] = TempTime
            txtElapsed = TimeToStr(TempTime)
        End If
    End Sub

Note: If the ElapsedTime field is required, you need to make adjustments in the BeforeUpdate procedure.

Put the following code in a standard module, so you can call it from multiple forms:

    Public Function TimeToStr(Time As Long) As String
        TimeToStr = Format(Fix(Time / 100) / 86400, "nn:ss") _
            & ":" & Format(Time Mod 100, "00")
    End Function
    
    Public Function StrToTime(ByVal TimeStr As String, ByRef Time As Long, _
                                ByRef ErrMsg As String) As Boolean
    ' Validates the string TimeStr, which has the format 'mmsshh' or 'mm:ss:hh'.
    ' If TimeStr is valid, returns True and returns the internal time value in
    ' the Time parameter. Otherwise, returns False and returns an error message
    ' in the ErrMsg parameter.
        Dim lngMins As Long, lngSecs As Long, lngHundredths As Long
        
        ' If string contains ":"s and is 8 chars long, convert to short format
        If Len(TimeStr) = 8 _
        And Mid(TimeStr, 3, 1) = ":" _
        And Mid(TimeStr, 6, 1) = ":" Then _
            TimeStr = Replace(TimeStr, ":", "", Count:=2)
        ' String must now be 6 chars long and all digits
        If Len(TimeStr) <> 6 Or Not IsNumeric(TimeStr) Then
            ErrMsg = &quot;Input must be in the format 'mmsshh'&quot;
        Else
            ' Extract minutes, seconds, and hundredths
            lngMins = Val(Left(TimeStr, 2))
            lngSecs = Val(Mid(TimeStr, 3, 2))
            lngHundredths = Val(Right(TimeStr, 2))
            ' Test minutes and seconds are in range
            If lngMins > 59 Then
                ErrMsg = &quot;Minutes must be 00-59&quot;
            ElseIf lngSecs > 59 Then
                ErrMsg = &quot;Seconds must be 00-59&quot;
            Else
                ' Compute result and return True
                Time = (lngMins * 60& + lngSecs) * 100& + lngHundredths
                StrToTime = True
            End If
        End If
    End Function

You can test TimeToStr from the Immediate Window. To test StrToTime, put the following code in a standard module and call it from the Immediate Window.

    Public Sub TestTime(s As String)
        Dim t As Long, msg As String

        If StrToTime(s, t, msg) Then
            Debug.Print "Internal value = "; t
        Else
            MsgBox msg
        End If
    End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Dan,

I see you still have this thread marked for email notification. Would you be a good Tek-Tips citizen and post to let us know if you solved your problem, and how, so that others may benefit? Thanks.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DanHughes]

HI There - yes I did manage to solve the problem.

I store the time as a number - double with 2dp set. I then put a format of 00\:00.00 on it. This displays it correctly, while storing a time like 1:23:97 as 123.97. This allowed me to calculate personal best by displaying minimum, but didn't allow me to calculate averages. Basically I was told I didn't need averages so it was all ok.