Convert Text Field to Date/Time

[GN]

I need to convert a text filed to date/time format. The text field is from
linked txt file. The field value appears like below......

Wed Jul 11 14:27:05 2007

Any help would be appreciated....Thanks!


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...920b5&dg=microsoft.public.access.externaldata

 

[Jerry Whittle]

IF all the text is fixed with the same format AND they are all valid dates,
something like the following will work:

NewDate: CDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

You might want to check for valide dates first. Something like this in a
query with the criteria of 0 or No will help you find any problem records.

NewDate: IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

I often check for bad dates and put something bogus in it if there's a
problem. Something like below will return 1/1/1950 if the string can't be
evaluated as a date.

NewDate: IIf(IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))=True, Cdate(Mid([TheField], 5, 6) & Right([TheField],
5) & Mid([TheField], 11, 9)), #1/1/1950#)

 

[GN]

Hi Jerry,

Thanks!, it's perfect,,,, works as intended, appreciate your feedback!

GN

IF all the text is fixed with the same format AND they are all valid dates,
something like the following will work:

NewDate: CDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

You might want to check for valide dates first. Something like this in a
query with the criteria of 0 or No will help you find any problem records.

NewDate: IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

I often check for bad dates and put something bogus in it if there's a
problem. Something like below will return 1/1/1950 if the string can't be
evaluated as a date.

NewDate: IIf(IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))=True, Cdate(Mid([TheField], 5, 6) & Right([TheField],
5) & Mid([TheField], 11, 9)), #1/1/1950#)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to convert a text filed to date/time format. The text field is from
linked txt file. The field value appears like below......

Wed Jul 11 14:27:05 2007

Any help would be appreciated....Thanks!

 

[Jerry Whittle]

Yes. You would need to import the data into a temporary table first then
append it to the final table. The append query would have the data conversion
code in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Where would you put that in Import code? If I am importing a text file to my
database table, and I use:

DoCmd.TransferText , "Header Import Specification", "HeaderTableName",
"HeaderFilePath"

but two of the fields in the Header table need converted. Will I need to
import them to a temporary table first and massage the data before adding
them to the production table?

IF all the text is fixed with the same format AND they are all valid dates,
something like the following will work:

NewDate: CDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

You might want to check for valide dates first. Something like this in a
query with the criteria of 0 or No will help you find any problem records.

NewDate: IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))

I often check for bad dates and put something bogus in it if there's a
problem. Something like below will return 1/1/1950 if the string can't be
evaluated as a date.

NewDate: IIf(IsDate(Mid([TheField], 5, 6) & Right([TheField], 5) &
Mid([TheField], 11, 9))=True, Cdate(Mid([TheField], 5, 6) & Right([TheField],
5) & Mid([TheField], 11, 9)), #1/1/1950#)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to convert a text filed to date/time format. The text field is from
linked txt file. The field value appears like below......

Wed Jul 11 14:27:05 2007

Any help would be appreciated....Thanks!