[RESOLVED] using hh:mm:ss counting format in Access

[Br1an_g]

i have an excel spreadsheet that has time in format:

hh:mm:ss

not in the sense of a time, in the sense of an incremental counter.

when i import to access, it does not recognise this format and instead formats as text.
if you change to time it changes it to am, pm time and removes any count that is greater than an expected time.

any way to get this to work in access? the spreadsheet is getting a bit big and cumbersome.

TIA

[RobDog888]

You probably need to define your access field as Date/Time with a custom Format as hh:nn:ss.
I did that and typed in 17:24:59 and it stayed. Now if your timer time is from 00 to 59, etc. you should be good to go.

[Br1an_g]

yeh, i tried that.

if you type in 25:25:25

it wont accept because it is outwith the 24 hour clock, but as a timer it needs to be able to do this..

anything else i can do? can i use a function to convert it from text to a timer? or take the 24hours away, then use the remainder to calculate?

e.g. 25:25:25, i take away the 24 hours to make 1 day, 1 hour 25min 25 secs.

the prob ive encountered is that it wont let me use any functions on a time over 24 hours..

help

[RobDog888]

Ok, its over 23 and 59 so I think the best solution is to store it as text in your format. Then you need to convert it
to time after reading it from the db and before populating the cell in Excel.

Using the Mod function is the way to go. I'll post an example in a minute.

[RobDog888]

Will the time ever be beyond 99:99:99?

[RobDog888]

Well, I just made a guess and assumed that it will. So here is a sloppy example. I'm sure you knew what I meant by
using Mod but I just wanted to complete the post.

Dim sTest As String
    Dim sDays As String
    Dim sHours As String
    Dim sMinutes As String
    Dim sSeconds As String
    Dim iStart As Integer
    Dim iEnd As Integer
    
    sTest = "25:83:98"
    iStart = InStr(1, sTest, ":")
    iEnd = InStr(iStart + 1, sTest, ":")
    sHours = Left$(sTest, InStr(1, sTest, ":") - 1)
    sMinutes = Mid$(sTest, InStr(iStart, sTest, ":") + 1, (iEnd - iStart) - 1)
    sSeconds = Mid$(sTest, InStr(iEnd, sTest, ":") + 1, Len(sTest) - iEnd)
    If CInt(sSeconds) > 59 Then
        sSeconds = sSeconds - 60
        sMinutes = sMinutes + 60
    End If
    If CInt(sMinutes) > 59 Then
        sHours = (sHours + ((sMinutes) / 60)) Mod 60
        sMinutes = sMinutes Mod 60
    End If
    If CInt(sHours) > 24 Then
        sDays = (sHours / 24) Mod 24
        sHours = (sHours - 24) Mod 24
    End If
    MsgBox sDays & " Day(s) " & sHours & ":" & sMinutes & ":" & sSeconds '1 Day(s) 3 Hours 23 minutes and 38 seconds

[Ecniv]

Just a thought:
Could you use API GetTickCount or similar as the number counting up?
Or use cdbl on the Now function to get a number?

The Now converted to a number can always be converted back to a date/time later on (including days)

The API GetTick is only an option, and probably wouldn't be useful as I think it runs from when the comp is turned on, but you may want to read up on it anyway?

[Br1an_g]

thanks for all of the suggestions.
ended up it was simpler than i thought.

access does actually increment above the 24hours, but it adds days to a date, so to get the actual hours i wanted i divided it by 24, put this into another text box overlapping the actual time it was showing as the minutes and seconds were always correct.

DatePart("h",Sum([TimeField]))/24

there were a couple of variations needed on the form to ensure it was correct, but that is the main part of it.

Thanks again

[dglienna]

If CInt(sSeconds) > 59 Then
sSeconds = sSeconds - 60
sMinutes = sMinutes + 60
End If

Shouldn't that be:

If CInt(sSeconds) > 59 Then
        sSeconds = sSeconds - 60
        sMinutes = sMinutes [B]+ 1
[/B]    End If