Need Function in Access for text conversion

[vaughny]

I've got a text field in Access which looks as follows:

5 02:05
or
11 06:59

representing elapsed time of 5 days, 2 hours and 5 minutes in the first case;
and
11 days, 6 hours, and 59 minutes in the second case.

I'd like to break this down to Days, Hours, Minutes in separate, NUMERICAL fields, so that I can do calculations on it.

I've figured out how to get the Days separated, using:
Day: Left([Time],InStr([Time]," "))

and figured out the minutes, using:
Min: Right([Time],(Len([Time])-InStrRev([Time],":")))

but I can't figure out how to get the hours part separated, then, how do I convert to numerical values?

Thanks!!

[RobDog888]

Since you can make then separated by spaces for each time part you can split the entire value to an array and then element 0 will be days, 1 will be minutes and 2 will be seconds.

You can CInt each element of the array if you need it in Integer data type.

Public Function SplitMe(ByVal sDayTime As String) As Variant
    Dim ar() As String
    ar = Split(sDayTime, Chr(32))
    SplitMe = ar
End Function

Private Sub WhateverTest()
    Dim ar() As String
    ar = SplitMe(Replace([Time], ":", Chr(32)))
    MsgBox "Days: " & ar(0) & " Hours: " & ar(1) & " Minutes: " & ar(2)
End Sub

[vaughny]

Awesome - thanks. I've not really used VB in access though so I probably need to learn a bit there and I assume I paste this into a Module and then let it rip?

[RobDog888]

Yes, you can paste the SplitMe funciton into a module as I made it a public function. The WhatererTest sub is just an example of its use.

[Phil Weber]

To convert a string to a numeric value, you may use the Val() or CInt() functions.

[vaughny]

Thanks guys.