numbers don't show up in a linked table, but do appear in an impo.

[Guest]

I'm linking to an Excel file, and one field containing numeric data
(formatted as general in Excel) appears in the linked table as #Num!. When I
import the same file, rather than link, the data comes in okay. I don't want
the user to have to import the file each time. How can I make the linked
table display the data correctly?

 

[John Nurick]

Hi Jan,

Normally the #NUM! error appears when the Jet database engine finds a
non-numeric value in a column that it has decided to treat as a number
field. So the first thing to suspect is that the values being displayed
as #NUM! are not actually numeric: could they be numbers formatted as
text (e.g. by being prefixed by apostrophes), or strings that look like
numbers (e.g. with a l instead of a 1)?

The solution is either to fix these values or to ensure that the entire
column is treated as text rather than numeric. For the latter, the
behaviour is controlled by the registry and other settings described at
http://www.dicks-blog.com/excel/2004/06/external_data_m.html .