#Num!

[dbigelow]

Help,

I have a linked Excel spreadsheet and one of my key columns is returning a #Num! in Access - note out of 400 records only 4 have a problem. In my queries Access see these fields as null. I have tried to use Nzfunction but the #Num! still remains - so I tried to use the Nz topopulate all of the null fields and then run another query to pull any remaining null fields but even though Access shows the field as a #Num! it treats the field as the value I assigned using the Nz function, 9 for this example. So I can't get rid of the NUM in Access and in Excel it shows as a blank field - if I clear contents in Excel then the field pulls through correctly in Access. However I will not be the one running the program and it only takes one #NUM! to blow the queries - help me please

 

[paulfenwick]

Hi

I also have this problem - I know what is causing it, but not how to solve it!

If you look at the linked table in Design mode you will see (I beleive) that that field is a number field when it should be text - hence anything Access cant convert to a number gives the error.

What I dont know is how to tell access to the link the field as text - It should as there are text items in there, and I cant see why some fields with both text and numbers are converted to text fields but some are not!

Any one have any suggestions?

 

[PHV]

Access make some assumptions about the field type depending of the firsts records values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[yetanotherjo]

If you need a linked table, you could try linking it with dummy all text data where you need a text field, then change your Excel sheet to the proper data, perhaps?