Need Function in Access for text conversion
I've got a text field in Access which looks as follows:
representing elapsed time of 5 days, 2 hours and 5 minutes in the first case;
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:
but I can't figure out how to get the hours part separated, then, how do I convert to numerical values?
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
Private Sub WhateverTest()
Dim ar() As String
ar = SplitMe(Replace([Time], ":", Chr(32)))
MsgBox "Days: " & ar(0) & " Hours: " & ar(1) & " Minutes: " & ar(2)
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?
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.
To convert a string to a numeric value, you may use the Val() or CInt() functions.
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
By angela_quests in forum VB Classic
Last Post: 04-13-2007, 05:57 AM
Last Post: 04-14-2006, 10:09 AM
By RossOliver in forum VB Classic
Last Post: 03-16-2006, 06:23 PM
By Mike Mitchell in forum .NET
Last Post: 09-13-2002, 06:41 PM
Last Post: 11-27-2001, 07:53 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL