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?
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 = ""
hms2c = retval
In the Query, I'd use:
SELECT Table1.hms, hms2c([hms]) AS Expr1
You'll probably find other uses for that function if you deal with these strings much.
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!