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 youSince 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|||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|||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|||In the VBA Editor, I'd addFunction 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 FunctionIn the Query, I'd use: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|||yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you|||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|||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