#Num! error message with excel/access linking

[AGOKP4]

Hi all,

I tried linking an excel table into an access db but I'm getting some #Num!
messages in some of the cells when I view the table in access. Some of the
numbers are <2 and 0.12.

Any help would be appreciated.

THANKS!!
agokp4

 

[KARL DEWEY]

Does your Excel cell have '<2' without the quotes? <2 is not a number.

 

[James A. Fortune]

Hi all,

I tried linking an excel table into an access db but I'm getting some #Num!
messages in some of the cells when I view the table in access. Some of the
numbers are <2 and 0.12.

Any help would be appreciated.

THANKS!!
agokp4

Maybe the contents of the following thread will help:

http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/41994/Num

James A. Fortune
(e-mail address removed)

 

[AGOKP4]

No, no quotes

Does your Excel cell have '<2' without the quotes? <2 is not a number.

 

[KARL DEWEY]

I think you did not understand me when I said that an entry in a cell as
below --
<2
is not a number. 3 is a number, 2 is a number, but a
field that has anything other than positive or negative sign with the
numerical characters is not a number.
An exception to that would be scientific notation like 3.50432-3 which
would translate to .00350432 as the minus 3 represents the number of decimal
places moved.

 

[Clif McIrvin]

Maybe the contents of the following thread will help:

http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/41994/Num

James A. Fortune
(e-mail address removed)

Thanks, James; I'd not seen that before!

I've compiled the following information about that #Num! error:

(Clif)

Here is some information I have gleaned over time regarding the #NUM!
error when importing or linking to Excel spreadsheets.

Excel stores cell values as a variant, and regardless of how you format
the entire column you can end up with different variant data types on a
cell by cell basis.

This becomes a nasty problem because the 'transfer spreadsheet' function
that Access uses to either import or link from an Excel worksheet does
not do any data type conversion. If you 'trick' Access into expecting
text data and one of the Excel cells has a number(or date) data type you
will get the #NUM error; conversely if you have everything formatted as
numeric(or date) and Access comes across a text datatype cell you will
get the #NUM error. (Empty cells properly come across as Null in either
case.)

I have also found that a cell containing a single space (in an otherwise
numeric or date column) will link as #Num!.


From MS Access MVP Roger Carlson:
Because I've had so much trouble with linking Excel files into Access
(previous to Access 2007, you had no control over the datatype), I've
taken to ALWAYS linking them as CSVs. That way I can define the
datatype in the Import Specification. (www.rogersaccesslibrary.com)


Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File (additional discussion of Jet, possible
registry hack)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr


Threads containing additional discussion:

http://groups.google.com/group/micr...read/809542461417afdf/7440746c9fa31c61?q=#num

http://groups.google.com/group/micr...read/b4b7748816387977/f8eaa8f9c95bc766?q=#num

Barry Gilbert discussing multiple data types in an Excel comumn:
http://groups.google.com/group/micr...roup:microsoft.public.access#a8b5bcd4f77450d9

This post by Mark shows a macro to force Excel cells to text data type
and another to force cells to a numeric type:
http://tinyurl.com/3vt5rm

Van T. Dinh finds the easy way is to modify the Excel file as follows:
* Insert a "calculated" Column next to the MixedColumn
* Link the Excel file to Access, ignore the original MixedColumn and use
the "calculated" Column. All values in this Column will be Text so the
values won't have #NUM entries.
His discussion is at http://tinyurl.com/4vf6uv .

One possible work-around is to use the procedure in this KB article to
force every cell to text type in the Excel sheet:
(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us

 

[KARL DEWEY]

I find that inserting a phony row just below the column names with data that
Access will interpert correctly solves the problem.

 

[Clif McIrvin]

I find that inserting a phony row just below the column names with data
that
Access will interpert correctly solves the problem.

As long as the columns don't switch data types. In my experience if a
column has multiple data types (number, date or text) the link will
still return #Num! for the offending data type (that's the data type of
the underlying variant in the Excel cell, not the data itself.)

If you happen to have a "well behaved" spreadsheet, I'm sure your're
right! <smile>

 

[user]

Hello

I am having the same problem. when i insert a row in excel and link it
again, it's still gives me #Num.
Please help. I have many fiels, some are text, #, date. I am trying to
convert text to numbers in excel and then trying to link excel to access, but
nothing seems to be working.

please help!

 

[Clif McIrvin]

Hello

I am having the same problem. when i insert a row in excel and link it
again, it's still gives me #Num.
Please help. I have many fiels, some are text, #, date. I am trying
to
convert text to numbers in excel and then trying to link excel to
access, but
nothing seems to be working.

Did you read the lengthy post you replied to? There are several
specific methods and work arounds discussed at length if you follow the
links.

Exporting Excel to .csv and linking to that should be pretty
straight-forward.

Post back with examples if you have specific questions.