Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / July 2006

Tip: Looking for answers? Try searching our database.

convert text to date/time or number

Thread view: 
Enable EMail Alerts  Start New Thread
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.