Type Mismatch

[Brenda from Michigan]

I have read about the Type Mismatch error when an Excel workbook has alpha
and numeric data is in the same field and is imported into Access 2002. I
have a user with what appears to be straight data who gets the Type Mismatch
error when importing her worksheet into a newly created database. If the
database already exists, the sheet will import fine. The data is composed of
text, SS#, and dates.

I also have Access 2002 SP3 and am having no trouble importing the same
workbook as my user. This make me think some setting may be involved. Can
anyone shed any light on this for me. Thanks!

 

[pietlinden]

I have read about the Type Mismatch error when an Excel workbook has alpha
and numeric data is in the same field and is imported into Access 2002.  I
have a user with what appears to be straight data who gets the Type Mismatch
error when importing her worksheet into a newly created database.   If the
database already exists, the sheet will import fine.  The data is composed of
text, SS#, and dates.

I also have Access 2002 SP3 and am having no trouble importing the same
workbook as my user.  This make me think some setting may be involved.  Can
anyone shed any light on this for me.  Thanks!

My guess is that the way that Access determines field types (by
looking at the first ~15 records of each column) is not always
accurate. So when you force Access to use "predetermined" field types
(because the table already exists), you don't have the problem. What
happens if you use an import specification?

 

[Brenda from Michigan]

I'm sorry but I don't understand what you mean by 'import specification.'

If you mean the process of assigning each field type during the import, that
only works for us if you import a delimited file. That screen includes an
Advanced button which will allow you to assign field types; otherwise, the
first field assigned is assigned to all subsequent fields. Is that what you
mean?

 

[Ken Snell \(MVP\)]

The error that you're seeing is because Jet (ACCESS) sees only number values
in the first 8 - 25 rows of data in the EXCEL sheet, even though you have
formatted the EXCEL column as Text. What ACCESS and Jet are doing is
assuming that the "text" data actually are numeric data, and thus all your
non-numeric text strings are "not matching" to a numeric data type. This
causes the "#Num!" error.

One way to "fix" this problem is to insert a ' (apostrophe) character at the
beginning
of each cell's value for that column in the EXCEL file. That should let Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric characters
in the cell in that column. That should let Jet (ACCESS) treat that column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Brenda from Michigan]

Thank you for the EXCELLENT information. I've passed it along to the user.