Link with Excel and #num! error

[Eva]

When I link tables with Excel I have the error message for some records (#num!)
I noticed that in one column I have both - numbers or text data.
I formatted the column to be general but in Access it still shows error for
number data

 

[Arvin Meyer [MVP]]

Access is not a spreadsheet and does not allow text in a numeric column no
matter how it's formatted. If you change the datatype to Text, you will be
able to use both numbers (as text) and text.

 

[KARL DEWEY]

It needs to be text. The easiest way I find is that before linking I add a
dummy row just below column names to define the data type. That way when
Access checks it will know what kind of field I want it to be.

 

[Ken Snell]

See this article for some suggestions:

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#DataTypeErr

 

[Rick Brandt]

See this article for some suggestions:

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#DataTypeErr

WHY doesn't the wizard for connecting to Excel simply provide a step where
you can specify the data-types instead of only relying on the "guessing"?
That is provided for text files and it completely solves this problem.

An embarrassment really. Access gets along with Excel worse than just about
anything else.

 

[Eva]

Thank you for your comments. It is very helpful. I am trying to import data
and hopefully it will work

 

[Ken Snell]

WHY doesn't the wizard for connecting to Excel simply provide a step where
you can specify the data-types instead of only relying on the "guessing"?
That is provided for text files and it completely solves this problem.

An embarrassment really. Access gets along with Excel worse than just
about
anything else.

ACCESS 2007 does provide this feature. Better late than never, eh?!