type conversion error

[THE BIG O]

This one is baffling me, probably enough to drive me to drink.

I am attempting to import data into a table. In the Excel file I formatted
the origin zip and destination zip as text. I also formatted the PRO# column
as text as well. I imported into access and I get the type conversion error
on Canadian postal codes and pro #'s that include a letter.

I then went back into the table and changed the data type to text. Deleted
all of the data out of the table and attempted the import again. Still the
same thing. I have not had the problem with other access databases where I
imported similar information. Always changed the data type and voila. Just
not this time.

I then tried the Link table and append to table option. Those fields that
were not imported are now showing a #Num!. I go back into the Excel file
and the three columns are still formatted as text.

What am I missing?

Michael

 

[Jerry Whittle]

Access has a very bad habit of checking the first few rows of the Excel
spreadsheet and assuming the data type. If there's a number or empty cell,
Access might say that it's a number field no matter what is in the cells
farther down.

One trick is to insert a bogus first row in Excel that had the correct data
types such as ABC for a text field. Import the spreadsheet then delete the
bogus record.

 

[THE BIG O]

That works. However this adds a couple of additional steps for the end
user---who by the way is access illiterate. It will be hard to explain why he
has to enter fake information.

thanks

Michael