#NUM! in linked field to Excel

[Guest]

I am trying to creat a Table linked to Excel and it all goes well according
to Access but when I look at the values in one field I see
ItemCode
#NUM!
7911
#NUM!
7921
7922
#NUM!
7931
7932
The records with #NUM! all end in 0(zero) both columns are formatted as
text( to support leading Zeros).
I have already tried the registry edit of the Jet 4.0 settings to set the
data type but this did not fix the problem.
The Linked spreadsheet is updated weekly so whatever I do needs to be able
to work without intervention.

 

[Brendan Reynolds]

Is it possible that the user may have used the capital letter 'O' rather
than the digit '0'? I see this quite often among users who used typewriters
before they started to use a computer.

 

[Guest]

I thought the same thing but I personally went in and changed the data using
my number pad. Access insists on calling it a number field even though the
Excel spreadsheet is formatted as Text

 

[Ken Snell \(MVP\)]

Linking to an EXCEL file leads to data type problems like this all the time.

In your EXCEL sheet, be sure that you have text strings in some of the first
few rows so that ACCESS / Jet will see values other than just numbers.