Text field to numeric

[skwilliams]

I need to import a field daily which is a text field. Here's an example of the information "0h 30m 22s". Would it be possible through code to automatically convert this to 1822 in a numeric field. I would like to calculate total seconds for a given time period but am unable to with the current text field.

Thanks.

 

[Crilen007]

well, there was a function that allowed detection of spaces, and splitting of fields (Ill try to find it).

You could separate the field into chunks, and remove the last part of it then do the calculations?

 

[sfreeman@co.mer]

OK, this function should work for you, but has not been thoroughly tested. It assumes that you will always have the designations h m and s in the input field, but does not assume any length of the values that precede the designations.

There may be a better way, but this does seem to work for any values that I test it with.

Public Function ConvertTime(strInput As String) As Long
Dim SearchString, SearchCharH, SearchCharM, SearchCharS As String
Dim MyPosH, MyPosM, MyPosS, intLen As Integer
Dim intHours, intMinutes, intSeconds As Integer
Dim intHlen, intMlen, intSlen As Integer
Dim intHrsSecs, intMinSecs As Integer

SearchString = strInput ' String to search in.
SearchCharH = "h" ' Search for "h".
SearchCharM = "m" ' Search for "m".
SearchCharS = "s" ' Search for "s".

MyPosH = InStr(1, SearchString, SearchCharH)
MyPosM = InStr(1, SearchString, SearchCharM)
MyPosS = InStr(1, SearchString, SearchCharS)

intHlen = MyPosH - 1
intMlen = (MyPosM - MyPosH) - 2
intSlen = (MyPosS - MyPosM) - 2

intHours = Nz(Left(SearchString, intHlen))
intMinutes = Nz(Mid(SearchString, (MyPosM - intMlen), intMlen))
intSeconds = Nz(Mid(SearchString, (MyPosS - intSlen), intSlen))

If intHours <> "" Then intHrsSecs = intHours * 3600
If intMinutes <> "" Then intMinSecs = intMinutes * 60
If Not IsNumeric(intSeconds) Then intSeconds = 0

ConvertTime = intHrsSecs + intMinSecs + intSeconds

End Function