Date/Time Type Conversion Failure

[Philip Papeman]

Hi Folks,

I'm having problems using the Access Import Table Wizard. I get a "Type
Conversion Failure" error on all date/time fields when trying to create a
new table. I've tried various options in the Import Specification dialog
box, but have had no luck.

A sample of the tab delimited text is below:

Yosemite Sam (e-mail address removed) 2006-Dec-27 11:02:06 AM 71.197.122.38
Daffy Duck (e-mail address removed) 2007-Jan-12 11:50:45 AM 63.200.186.196
Bugs Bunny (e-mail address removed) 2007-Jan-14 6:53:27 PM 71.197.122.38
Roadrunner (e-mail address removed) 2007-Jan-23 5:56:36 PM 71.197.122.38

Any advice greatly appreciated. Thanks.

Phil

Windows XP Pro SP2
Microsoft Access 2003

 

[Jerry Whittle]

You'd think that it would work with an import spec set to YMD and - date
seperator, but it doesn't.

Import the data into a temporary table with your date field set as text.
Then do an append query to where you want to data. Use something like the
following to convert the text to a date.

ConvertedDate: IIf(IsDate([field3])=True,CDate([field3]),#1/1/1950#)

If IsDate finds something that can't be evaluated as a date, the IIf
statement inserts the bogus 1/1/1950 instead. You can then look for them and
manually fix any problems.

 

[Tom Wickerath]

Hi Phillip,

In addition to Jerry's input, I found the following by experimenting:

1.) The Access Text import wizard seems to require that the date be in
month-day-year format (at least on my PC, but perhaps that's related to my
regional Date/Time setting in the Control Panel).

2.) It also seems to require that time be in the 24-hour format, without the
AM or PM included.

The following re-arrangement of your data imports without a problem directly
into Access (on my PC):

Yosemite Sam (e-mail address removed) Dec-27-2006 11:02:06 71.197.122.38
Daffy Duck (e-mail address removed) Jan-12-2007 11:50:45 63.200.186.196
Bugs Bunny (e-mail address removed) Jan-14-2007 18:53:27 71.197.122.38
Roadrunner (e-mail address removed) Jan-23-2007 17:56:36 71.197.122.38

One thing I found is that the import wizard in Excel is a lot better. You
can easily import your data into Excel, save it as a .XLS file, and then
import this directly into Access. However, the idea that Jerry presented is
easily implemented too, and the manner in which I usually deal with import
problems.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

You'd think that it would work with an import spec set to YMD and - date
seperator, but it doesn't.

Import the data into a temporary table with your date field set as text.
Then do an append query to where you want to data. Use something like the
following to convert the text to a date.

ConvertedDate: IIf(IsDate([field3])=True,CDate([field3]),#1/1/1950#)

If IsDate finds something that can't be evaluated as a date, the IIf
statement inserts the bogus 1/1/1950 instead. You can then look for them and
manually fix any problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Folks,

I'm having problems using the Access Import Table Wizard. I get a "Type
Conversion Failure" error on all date/time fields when trying to create a
new table. I've tried various options in the Import Specification dialog
box, but have had no luck.

A sample of the tab delimited text is below:

Yosemite Sam (e-mail address removed) 2006-Dec-27 11:02:06 AM 71.197.122.38
Daffy Duck (e-mail address removed) 2007-Jan-12 11:50:45 AM 63.200.186.196
Bugs Bunny (e-mail address removed) 2007-Jan-14 6:53:27 PM 71.197.122.38
Roadrunner (e-mail address removed) 2007-Jan-23 5:56:36 PM 71.197.122.38

Any advice greatly appreciated. Thanks.

Phil

Windows XP Pro SP2
Microsoft Access 2003

 

[Philip Papeman]

Jerry and Tom,

Thanks very much for the help. Greatly appreciated.

Phil

 

[Jerry Whittle]

Judging by the names used in your example, shouldn't you have said:

Tom and Jerry,

;-)