ACC: Conversion Errors Importing Dates with Different Formats

• Microsoft Access 7.0, 97

SYMPTOMS

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

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)

REFERENCES

For more information about update queries, search the Help Index for "update queries," and then "change records as a group using an update query."

For more information about importing, search the Help Index for "Importing Data," or ask the Microsoft Access 97 Office Assistant.