Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: char to total time

    I must use a database with strange columns ( I cannot change it) ... one column store time into a string format (char) >>>

    4 m 42 s
    1 m 10 s

    and I must get the total of seconds !!

    then how can I get with >>>
    4 m 42 s (= 282)
    1 m 10 s (= 70)

    a total = 352

    ??

    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since no two database engines seem to handle strings quite the same way, which engine are you using? Are the columns limited to just minutes and seconds, or can they add hours, days, fortnights, or other units of time? Is the formatting fixed (always two digit seconds), or can it vary? Are minutes required or optional?

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    18
    it is for ACCESS 2000

    and in the database are only m and s
    but I think it is possible to find
    4 h 8 m 24 s

    nothing else !
    maximum are hours

    thanks a lot if you can find

  4. #4
    Join Date
    Mar 2004
    Posts
    18

    error

    I have tried

    Table1 is the table
    hms is the column

    SELECT Sum(Left([hms],InStr(1,[hms],"m")-1)*60+Mid([hms],InStr(1,[hms],"m")+2,2)) AS sumOfSeconds FROM Table1;


    but it doesn't work

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In the VBA Editor, I'd add
    Code:
    Function hms2c(hms As String) As Integer
    '  ptp  20040404  Covert "[ x h][ y m][ z s]" string to integer seconds
    
    Dim retval As Integer   ' return value
    Dim c As String         ' current character
    
    retval = 0: d = "": hms = LCase(hms)
    
    While hms <> ""
        c = Left(hms, 1): hms = Mid(hms, 2)
        If 0 < InStr(1, "0123456789", c) Then d = d & c
        If "h" = c Then retval = retval + 3600 * Val(d): d = ""
        If "m" = c Then retval = retval + 60 * Val(d): d = ""
        If "s" = c Then retval = retval + Val(d): d = ""
    Wend
    
    hms2c = retval
    End Function
    In the Query, I'd use:
    Code:
    SELECT Table1.hms, hms2c([hms]) AS Expr1
    FROM Table1;
    You'll probably find other uses for that function if you deal with these strings much.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Posts
    18
    yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

    thank you

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by castali
    yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

    thank you
    What exactly do you mean by "outside"? everything I've suggested is from pure Access 2000. Unless you are using Office 2003 (aka Office.NET), you can't use VB.NET from within Access.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The solution offered by schlauberger is interesting, but it only works for very limited cases. It will fail if there are hours, or if either the minutes or the seconds are missing. If that works for your needs, enjoy!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •