convert text to date/time or number

[Guest]

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]

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.

 

[Guest]

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

 

[Douglas J. Steele]

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

 

[Guest]

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!!

 

[Douglas J Steele]

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!

 

[Guest]

got it...thank you so much.

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!

--
Doug Steele, Microsoft Access MVP

(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.

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!!