What is #Num! error

[Guest]

I am trying to link an EXCEL sheet to ACCESS. I think that I have a format
problem with some of my numbers but I do not know how to create a column that
can have both text and numbers, and the numbers can still be sorted. Most of
the numbers in the column are o.k. but any new ones I have added recently do
not appear. Instead I get #Num! Do I need to set the format in EXCEL and
should it be "General" or "Text"?

 

[Ken Snell \(MVP\)]

When you link to an EXCEL spreadsheet from ACCESS, Jet (the database engine)
reviews the first 8 to 25 rows of data in the spreadsheet and decides what
the data type is. If there are no nonnumeric characters in those initial
rows, Jet will assign a numeric data type. Then, for rows farther down with
nonumeric characters, ACCESS will display the #Num! error because those
strings are not numeric.

With linking, your have two choices involving changes to the EXCEL
spreadsheet:
1) Put nonumeric characters in the first row of the spreadsheet.
2) Put an ' character in front of every value in the appropriate
column for the spreadsheet. That tells Jet that the value is a text value
and not a numeric value.

You can make changes to the Registry itself that will force Jet to scan all
the rows before deciding on a data type. See this article for information
about how to change the MaxScanRows property to the value of 0:
http://www.dicks-blog.com/excel/2004/06/external_data_m.html

 

[Guest]

Thank you very much. I was getting to this solution, but using "format
painter" but I think that the ' solution is much better.
Thank you!
Brenda