Query #NUM!

[Joshwenny]

I have a calculated row in a table that when no values are there to be calculated, displays a #num!. Is there a way to make that cell just be blank "Null" when there are no values to calculate. The reason its an issue is that I cannot create a totals query for that row because of the #NUM!. I want to be able to average that row using a query but cannot because of the #num! error that is displayed in the row. Any help is much appreciated. Thanks!

[June7]

What is the expression?

[Joshwenny]

I should state that the #num! is coming from dividing by zero. I have a calculation that counts any yes statements as 1 and any no statements as 1 and then I use a calculation to find the percentage of questions answered correctly. [Question 5 Correct]/([Question 5 Correct]+[Question 5 Incorrect]) When both the yes and no rows are zero, I get a #num! error. How can I prevent this so that way I can use a totals query to average that column? It will not let me do that with rows that have a #num!. I need a way to somehow say, when both Question 5 Correct] & [Question 5 Incorrect]) are zero, then show as null. Is this possible?

[davegri]

Let's try again. WHAT is the code causing the trouble? Not a description of what you want the code to do, but the CODE ITSELF. How do you expect us to debug code that you refuse to show?

EDIT: Josh, just notice how close together the timing is in the posts. Maybe I jumped the gun on you, as you may not have yet seen post #2 when you sent post #3

[Joshwenny]

I guess I'm unsure of what code you mean? I don't think there is code "SQL" for tables. Are you needing the error message that pops up when I try to run the query? The query is a totals query that is trying to average a row that contains a few #num! values. Those #num! values are what i'm trying to have changed to null values.

[davegri]

I have a calculated row in a table that when no values are there to be calculated, displays a #num!.

Those #num! values are what i'm trying to have changed to null values.

Yes, and what calculations/code is causing the #num values? Where are the fields involved in the calculations coming from? Give us something to work with.

[June7]

[Question 5 Correct]/IIf([Question 5 Correct]+[Question 5 Incorrect] = 0, 1, [Question 5 Correct]+[Question 5 Incorrect])

[Joshwenny]

June7, THANK YOU!!!!!!! Your equation worked great. I changed the equation to use null instead of 1 and viola! [Question 5 Correct]/IIf([Question 5 Correct]+[Question 5 Incorrect]=0,Null,[Question 5 Correct]+[Question 5 Incorrect]) Both you and Davegri, thank you!