#num! errors

[Malb]

I have a linked table which will contain errors and which give an #NUM! error.
That's fine. I can't clean up the data I'm linking to (not in my control) but I know that the #NUM! error items can just be ignored for my reporting purposes - they don't affect the totals I am trying to extract.

The problem is how?
I tried correcting them through a query e.g
SELECT ... IIF(IsNumeric(FunnyField),[FunnyField],0) AS CorrectedFunnyField ....
but the #NUM! error just proprogates through.

Similarly IIF(IsError(....
doesn't detect the #NUM!

and IIF(FunnyField=#Num! ... or IIF(FunnyField='#Num'...
doesn't work ... not that I expected it to.

I'm at a bit of a loss. How do I stop #NUM! errors propagating through to my reports?

 

[DCrake]

As a suggestion make a copy of the table and change the field data type to text and see what results are returned. You could then use Val() around the "Number" fields to perform caclulations.

Where is the data being sourced?
What is causing the #Num?
What datatype are you currently using on this field?
Does it match the incoming data type?

David

 

[editolis]

Maybe your table is not Access table but is Excel connection.

Thats why you have num# errors.

Is that correct?

 

[Malb]

Sorry, yes, it is an Excel table.

Taking a copy would be a possibility but then I would have delete the whole table and re-read it periodically thus defeating the point of linking the data rather than importing it. Also there are a number of tables involved and it is easier to switch links and refresh the links than to constantly reimport new data.

I really would therefore prefer a linked table as stated in the question.

 

[editolis]

It is very easy to transfer excel sheets and create access table to your database. I am not so advanced in access myself but THIS is not so difficult to do it.

Here is the code to transfer the excel sheet.

Create a button to your form and put this code:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="GIVE_ANY_NAME_TO_TABLE", _
FileName:=Me!GIVE_THE_LINK_FOR_YOUR_XLS, Hasfieldnames:=True, _
Range:=("GIVE_THE_EXCEL_SHEET_NAME!"), SpreadsheetType:=5

Then create another button and put this code to delete the Excel-ACCESS table:

Dim strSQL
strSQL = "DROP TABLE GIVE_ANY_NAME_TO_TABLE"
CurrentDb.Execute strSQL, dbFailOnError

So transfer fresh data and delete old data that is the best choice for my opinion my friend for you to not have #num! errors.

 

[Malb]

I can transfer sheets into Access. The question I asked was about linked sheets and I really would like an answer to that question, if there is one. Surely Access must have some way of detecting #Num! errors?

If you tell me it does not, and that Microsoft have provided no way to stop #Num! errors proprogating throughout a database, then obviously, I will look for other solutions.

But it is answer to that question I am really searching.


---------------
Incidentally I would rather dispute your "it is easy to transfer".
Try your example with a simple range, for example:
Range:="Sheet1!$E$2:$H$4"
Bet you it will fail with a runtime error 3011 and tell you that the range does not exist.
Despite the fact that you can see Sheet1 and the cells clearly do exist.
Took me hours to crack that problem when I first encountered it because the helpfiles mention it nowhere I could see

 

[boblarson]

Actually, I think you have a few things confused.

1. You can transfer specific ranges and/or cell values into Access if you use the Excel Object Model (and by that I mean something like:

Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
etc...

I won't go into it right now as I don't have time to type it all out. There are several examples all over the site regarding this however.

2. Access, when you link to a data source, will display what it can. If there are things it cannot display or figure out then it will show something like #Num. It is up to YOU to deal with those and not Access. But you first have to figure out what it is that is causing Excel to show as #Num. And, if you could post a sample spreadsheet which has data like that in it, we might just be able to figure it out for you so then you can "handle it" in your database and thereby making it LOOK cleaner. But, that is the important part. You have to know what it is in Excel that is causing it.

 

[neileg]

Just to add to Bob's advice:

Excel is not at all fussy about datatypes and you can populate the cells with any old rubbish. Access is fussy about datatypes as you have found out. When you link to the Excel table, Access will assume what the datatype is based on the first few records. That assumption may be wrong which is why you get the #NUM error. This is one of the reasons why linking to Excel tables should be avoided if at all possible.

As Bob suggests, you need to cleanse the data in Excel so that the errors are removed. The problem will return, however, because the users who populate the Excel table will get it wrong, sure as eggs are eggs.

 

[gemma-the-husky]

the issue could be

a) text in a number column

or 12 digit numbers
b) numbers too big. excel seems to accept any length of number, and work with it happily. but if its an integer, access will stop at maxlongint ie 2^31, effectively about 2billion - which means that ANY 12 digit excel number, will not represent correctly in access. (in a long field)

 

[editolis]

Probably his values at the Excel sheet is something like this: 12-8 or 9-1 or whatever similar.

So what happen next is that excel assumes that this values are dates.

Thats we he have #num! errors.

I Had the same problem recently.