ACC2000: Conversion Errors Importing Dates with Different Formats (208591)

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

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see 296572.

SYMPTOMS

When you import a fixed-width file with two Date fields that have 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. Access does not allow you to import a Date field that does not have delimiters together with a Date field that does have 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)

REFERENCES

For more information about update queries, click Microsoft Access Help on the Help menu, type "change records as a group using an update query" in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For more information about importing, click Microsoft Access Help on the Help menu, type "Importing Data" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.