Microsoft KB Archive/142184

= ACC: Conversion Errors Importing Dates with Different Formats =

Article ID: 142184

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q142184



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you import a fixed-width file with two Date fields with different formats, individually you can import them, but if you try to import them together you may receive the following error message:

Type Conversion Error



CAUSE
You may receive this error message when one Date field has no delimiters and the other Date field is formatted as a standard Date/Time field. Microsoft Access 7.0 and 97 do not allow importing a Date field without delimiters together with a Date field containing delimiters. You must import the non-delimited Date field as Text and then convert the data to a standard Date format.



RESOLUTION
If you want to import a file that contains data formatted in this manner, you must either edit the fixed-width field to add the / (slash mark) delimiter or import the field as Text and then use an update query to convert the Text to a Date format.

For example, you can use the following expression in an update query to convert a Text field containing 010196 to a Date format of 01/01/96. You can then change the field in Table Design view from a Text field to a Date field.

  Left$([Mydate],2) & "/" & Mid$([Mydate],3,2) & "/" & Right$([Mydate],2)

