Type Conversion Failure

[Aaron]

I'm trying to import data from an Excel file to an Access table. When I
try to do this, I'm told that all data was not successfully imported.
Upon viewing the error log, I'm told that that there was an error for
many of the records due to a "Type Conversion Failure" for one of the
fields (a numeric field). There is no apparent reason for this. In
Excel, I've made sure that the whole column with these numbers has a
"numeric" cell type. There is no difference between any of these cells,
but Access seems to think there is. I see no rhyme or reason why it is
being so discriminatory. Does anyone out there have any ideas as to
what is causing this mess?

Thanks,
Aaron

 

[Arvin Meyer]

If any of the numbers are formatted with commas or currency symbols, Access
will read them as text or currency.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Aaron]

If any of the numbers are formatted with commas or currency symbols, Access
will read them as text or currency.

None of them are...they're all just a simple 2 digit number which is
formatted in Access to being 4 digits (e.g. 45 becomes 0045). Any other
ideas?


Thanks,
Aaron

 

[Arvin Meyer]

0045 is not a number, it is text, hence the type mismatch. There are no
numbers (other than 0) which begin with 0.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Aaron]

0045 is not a number, it is text, hence the type mismatch. There are no
numbers (other than 0) which begin with 0.

Maybe I misstated myself...
In Excel, the aforementioned column consists of numeric cells, and each
has a two digit number. This column correlates (upon import) with a
numeric field in Access, a field that has it's format property set to
0000, which means that when a number XX is entered into that field, it
will appear as 00XX. This does remain a number, as far as I know.

Aaron

 

[Aaron]

Maybe I misstated myself...
In Excel, the aforementioned column consists of numeric cells, and each
has a two digit number. This column correlates (upon import) with a
numeric field in Access, a field that has it's format property set to
0000, which means that when a number XX is entered into that field, it
will appear as 00XX. This does remain a number, as far as I know.

Aaron

Well, I just tried to do the import after removing the 0000 format for
the problem field, and I get the same result (a Type Conversion
Failure). Apparently this has nothing to do with the formatting
property in this case.....

Aaron

 

[Arvin Meyer]

Just to prove me right or wrong, are the "numbers" on the right or left of
the cell? Now, take the column and copy/paste it into a new spreadsheet. Are
the "numbers" on the right or left of the cell?

Text is always Left justified. Numerics are always Right justified.

If you can enter the "number" in Access as 00nn it is always text. No
exceptions. There is no such thing as a number other than 0 that begins with
0. You did not mis-state the problem.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

If you can enter the "number" in Access as 00nn it is always text. No
exceptions. There is no such thing as a number other than 0 that begins with
0.

Arvin, you can of course set the Format property of a Number field or
a textbox to 0000. It will *display* the leading zeros - to the user,
it is a number which they can enter as 00nn. I agree, the leading
zeros aren't stored, but it's a bit misleading to say that "there is
no such thing as".

 

[John Vinson]

Well, I just tried to do the import after removing the 0000 format for
the problem field, and I get the same result (a Type Conversion
Failure). Apparently this has nothing to do with the formatting
property in this case.....

I wonder if there are one or more rows in the spreadsheet which in
fact do contain text values - perhaps with the letter O instead of a
numeric zero, or a letter L in place of a 1. You might try importing
the spreadsheet into a new table with a Text field, and run a query
searching that field for non-numeric characters; a quick way to do so
is to use the IsNumeric() function:

SELECT * FROM tablename
WHERE NOT IsNumeric([fieldname]);