#Num! Error

[BiotinX]

All -

I have a sheet that has cells that have data (numbers) and some that do not. When doing a sum, I'm getting a #Num! error I believe because access is reading the "empty" cell as 0 and consequently giving 0/0 = !Num#.

To eliminate the error (because I'm trying to link this query to an excel sheet) I have tried IsError nested in an if statement, which did not work. I also tried using IsEmpty to distinguish between an empty cell and a cell with a variable, but both return the same value.



Help!

[ranman256]

you would have to query only the values with non zeros. then divide.

[BiotinX]

Sometimes the values are zero and sometimes they are not, I don't want to make a separate query because that would jack up my column organization in the excel sheet this feeds into. There has got to be a way to suppress the !Num# or turn it into a 0.

[Micron]

This error is being raised by a query?
Post sample of data where it works and a sample where it doesn't, and the query expression that isn't working or try something like
IIF(valueB = 0,"",valueA/valueB) or Null instead of "" in such an expression.

IsEmpty is for testing if an object variable is empty or contains an object.

[BiotinX]

Yes, I have query that's doing some math. Here what I have in the query -
Q21: Sum(IIf([q_Compile3]![Q21_Score]=4,1,0))/Count([q_Compile3]![Q21_Possible])*100

I have a lot of fields with this code, anywhere between 25-100 depending on the number of questions on the test. The error comes in when there is no question 26 for example, [q_Compile3]![Q26_Score] and [q_Compile3]![Q26_Possible] have no value in that field.

I'm trying to build my input to this query so that the empty fields will be populated by zeros. Then I can add an additional if statement before the calculation above.

[Micron]

So if the first value isn't 4 (can be anything else) you'll end up with 0. Then isn't the problem with the latter part? You can't divide by 0 so 'empty' fields being 0 won't help. Maybe you need an expression that divides if the reference is greater than 0 but does nothing if it is 0. Could it be negative also?
Maybe
Sum(IIf([q_Compile3]![Q21_Score]=4,1,0))/IIF(Count([q_Compile3]![Q21_Possible])> 0,Count([q_Compile3]![Q21_Possible])*100,???)
??? is whatever you'd use as a divisor - perhaps 1. Perhaps null.

You could consider writing a function that returns a variant and call it from the query field. Then if the function sees a zero or null in the second part, it could simply return null to the field (assuming the query/table field allows that) and not do any math at all. I suppose you could find a way to write your expression to do the same, but I think a function is a bit more elegant and often more versatile.