char to total time

castali · #1 (**permalink**) 04-05-04, 15:53

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

Pat Phelan · #2 (**permalink**) 04-05-04, 17:00

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

castali · #3 (**permalink**) 04-05-04, 23:34

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

castali · #4 (**permalink**) 04-05-04, 23:46

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

Pat Phelan · #5 (**permalink**) 04-06-04, 07:52

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

castali · #6 (**permalink**) 04-06-04, 08:54

yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you

Pat Phelan · #7 (**permalink**) 04-06-04, 10:25

Quote:

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

Pat Phelan · #8 (**permalink**) 04-06-04, 14:31

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

Internet Services

Web Development

Digital Marketing

Consumer Tech