MS Access Forum / New Users / July 2006
convert text to date/time or number
|
|
Thread rating:  |
joe - 05 Jul 2006 20:11 GMT I have a text field which stores data as 00 Days 00 Hours 00 Minutes. I need to convert this data to a Date/Time...I think? or maybe a number?
What I need is to have an end result in the format of 00.0 (where 00.0 is hours). For example if my original value was 00 Days 08 Hours 10 Minutes, that needs to become 8.1666 (10/60 = .1666).
Any takers? I don't think this is too hard, but its casused me to get a rash I think.
Douglas J Steele - 05 Jul 2006 20:54 GMT Assuming that the text field will always have 2 digits, a space, the word Days, a space, 2 digits, a space, the word Hours, a space, 2 digits, a space and the word Minutes, try something like the following untested air-code:
Function ConvertTime(InputValue As String) As Single Dim strDays As String Dim strHours As String Dim strMinutes As String
strDays = Left(InputValue, 2) strHours = Mid(InputValue, 9, 2) strMinutes = Mid(InputValue, 18, 2)
ConvertTime = CLng(strDays) * 24 + _ CLng(strHours) + _ CLng(strMinutes) / 60#
End Function
If it's possible that the number of digits in front of Days might vary, post back.
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
> I have a text field which stores data as 00 Days 00 Hours 00 Minutes. I > need to convert this data to a Date/Time...I think? or maybe a number? [quoted text clipped - 5 lines] > Any takers? I don't think this is too hard, but its casused me to get a > rash I think. joe - 05 Jul 2006 22:53 GMT unfortunately, I copied the function to get this data from a MS site. The data varies now that I look at it closer...below are two examples. I guess sometimes the days, hours and minutes have 2 digits and sometimes 1.
0 Days 1 Hours 6 Minutes 0 Days 19 Hours 40 Minutes
> Assuming that the text field will always have 2 digits, a space, the word > Days, a space, 2 digits, a space, the word Hours, a space, 2 digits, a space [quoted text clipped - 27 lines] > > Any takers? I don't think this is too hard, but its casused me to get a > > rash I think. Douglas J. Steele - 05 Jul 2006 23:46 GMT Function ConvertTime(InputValue As String) As Single Dim intDaysP As Integer Dim intHoursP As Integer Dim intDays As Integer Dim intHours As Integer Dim intMinutes As Integer
intDaysP = InStr(InputValue, " Days ") + 6 intHoursP = InStr(InputValue, " Hours ") + 7
strDays = Val(InputValue) strHours = Val(Mid(InputValue, intDaysP, 2)) strMinutes = Val(Mid(InputValue, intHoursP, 2))
ConvertTime = CLng(strDays) * 24 + _ CLng(strHours) + _ CLng(strMinutes) / 60#
End Function
This assumes that it's always Days, never Day, and always Hours, never Hour. If that assumption is wrong, try replacing
intDaysP = InStr(InputValue, " Days ") + 6 intHoursP = InStr(InputValue, " Hours ") + 7
with
intDaysP = InStr(InputValue, " Days ") + 6 If intDaysP = 0 Then intDaysP = InStr(InputValue, " Day ") + 5 End If intHoursP = InStr(InputValue, " Hours ") + 7 If intHoursP = 0 Then intHoursP = InStr(InputValue, " Hour ") + 6 End If
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)
> unfortunately, I copied the function to get this data from a MS site. The > data varies now that I look at it closer...below are two examples. I [quoted text clipped - 41 lines] >> > a >> > rash I think. joe - 06 Jul 2006 13:16 GMT This is great! However, I'm not sure quite how to use it :) I created a new module, copied the function into it.
I have a query that creates a new field and I would like the function to populate that new field. What do I put in the query?
NewField: ConvertTime (now what?, the field name of the field which contains the data to be converted?).
The second question is that I tried the code in the immediate window. I typed
?ConvertTime (ElapsedTime) -ElapsedTime is my field formated with 00 Days etc.... When I do so I get a compile error ByRef Argument Error
If I type
?ConvertTime (00 Days 00 Hours 00 Minutes) I get the msg. Compile error: Expected: list separator or )
I'm sorry for all the questions!!
> Function ConvertTime(InputValue As String) As Single > Dim intDaysP As Integer [quoted text clipped - 78 lines] > >> > a > >> > rash I think. Douglas J Steele - 06 Jul 2006 14:12 GMT As long as you didn't name the module ConvertTime, that should be all you need to do.
Actually, though, I just noticed there are some errors in my code. (I made some changes to variable names, and didn't propagate the changes everywhere). The function should be:
Function ConvertTime(InputValue As String) As Single Dim intDaysP As Integer Dim intHoursP As Integer Dim intDays As Integer Dim intHours As Integer Dim intMinutes As Integer
intDaysP = InStr(InputValue, " Days ") + 6 intHoursP = InStr(InputValue, " Hours ") + 7
intDays = Val(InputValue) intHours = Val(Mid(InputValue, intDaysP, 2)) intMinutes = Val(Mid(InputValue, intHoursP, 2))
ConvertTime = CLng(intDays) * 24 + _ CLng(intHours) + _ CLng(intMinutes) / 60#
End Function
Actually, if you're going to be using this with a table, where it's possible that fields you're passing to the function might contain Null values, you should probably change that to:
Function ConvertTime(InputValue As Variant) As Single Dim intDaysP As Integer Dim intHoursP As Integer Dim intDays As Integer Dim intHours As Integer Dim intMinutes As Integer
If Not IsNull(InputValue) Then intDaysP = InStr(InputValue, " Days ") + 6 intHoursP = InStr(InputValue, " Hours ") + 7
intDays = Val(InputValue) intHours = Val(Mid(InputValue, intDaysP, 2)) intMinutes = Val(Mid(InputValue, intHoursP, 2))
ConvertTime = CLng(intDays) * 24 + _ CLng(intHours) + _ CLng(intMinutes) / 60# End If
End Function
To use this in a query, simply put ConvertTIme([MyField]) in the query. (replace MyField with the actual field name)
As to your other problem, when you say "ElapsedTime is my field formated with 00 Days etc...", are you saying that you've created a module that has a variable named ElapsedTime in it? How did you declare the variable? The function is expecting it to be declared as a string: if you're not explicitly declaring variables, ElapsedTime would be a variant, and you'll get that problem. The second version of the function above will solve that problem.
If you're not explicitly declaring variables, you should be. If you haven't got Access set up to force you to declare variables, so that it automatically puts "Option Explicit" at the top of each module, you should. (you do this on the Module tab through Tools | Options while in the VB Editor). Doing so will vastly decrease the amount of time you need to spend on debugging!
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
> This is great! However, I'm not sure quite how to use it :) I created a new > module, copied the function into it. [quoted text clipped - 99 lines] > > >> > a > > >> > rash I think. joe - 06 Jul 2006 16:11 GMT got it...thank you so much.
> As long as you didn't name the module ConvertTime, that should be all you > need to do. [quoted text clipped - 183 lines] > > > >> > a > > > >> > rash I think.
|
|
|