#Num! in linked Excel file

[Dkline]

I have a linked Excel file. Within that file is a field/column of numbers.

When I look at it in Excel - I see the numbers.

When I look at in Access - I see #Num! When I see it in the Form, I don't -
the field is blank.

What is the #Num! telling me - that it is not a number? If it's a number in
Excel, why wouldn't Access see it as a number.

The numbers are not overly large. Range is 1,000,000 to 100,000,000.

 

[Roger Carlson]

Is the column mostly blank? With this number appearing a hundred or more
records down? If so, it is a problem that I have experienced with linked
Excel tables too. It seems that when it is linked, Access looks at the
first hundred or so records to see what data type it is. If it finds no
values, it makes it Text. If it subsequently runs into numbers after it is
linked, it displays #NUM.

I don't have any real good solution for this. You can't change the datatype
after linking, and you can't define it as number during the linking. (At
least I've not found a way to do so.) The only solution I have found is to
physically move a whole row that has a value in this column to the top of
the sheet. Then it will read the datatype correctly.

If anyone has a better solution, I'd love to hear it.

 

[Lynn Trapp]

Roger,
If memory serves me correctly, Access actually searches the first 10 or 15
rows to determine a data type on a link or import. Thus, if it finds text in
ANY of those test rows it will make the column a text datatype. I don't know
for sure how it handles NULLs in those first rows, but probably as you
suggest.

 

[Roger Carlson]

You may well be correct. Every time I had this problem the first non-null
record much farther down.

An other thing I tried to correct this problem was to move a record to the
top, link the table, then move the record back to it's original spot. No
good! The same thing happens. Apparently everytime the database is opened,
it re-defines the datatype of the Linked sheet. Maddening!

 

[Dkline]

Thank you to both for the solution.

The last thing my macro did was to sort the Excel file on the key field.
This had the net effect of putting ALL the records without a value in that
column at the top.

I remmed out the sort, reran it, and voila! Numbers! The field in row 1 had
a number so all the rest were numbers in the records that had a number in
the field.

As the macro is processing, for those records in which I know this field
should NOT have a value, I am setting the value to "". Should I be using
something else? Is there a value I can assign that equates to NULL in
Access?

So again thank you. Next concen though is - is there no way to tell Access
that this column in the linked spreadsheet is a $ amount or currency?
Something that can be done by macro or VBA?