Using VBA to avoid the output of #Error on a report

[dayna]

I have a report (based on a query) that analyzes student progress on the GED test. Although the report functions perfectly most of the time, there is a rare exception that causes a calculated field in my query to be divided by zero, resulting in the display of #Error in a textbox on my report.

I have thought it through long and hard, and I am simply not clever enough to think of a mathematical way out of this problem. Rather than going into a deep explanation of all the rules and bureaucracy that govern this testing process, and why I have to sometimes divide by zero, I wonder if anyone can help me manipulate the output of that textbox in those rare cases that result in #Error.

For example, I tried creating an additional calculated field in my query…

Second Field: IIf ([First Field] = “#Error”, default value, [First Field])

…thinking I could base the textbox in my report on the Second Field. Alas, the Second Field also results #Error. I also tried it without the quotation marks, but I get an error message warning me about an invalid date value or something like that.

Thus far I’ve only used very basic VBA to improve the way my forms function, but I imagine it could be used tackle this problem, if #Error can even be referred to as a value. My issue seems to be that I lack the vocabulary to describe my goal, which has rendered my searches long and fruitless. Your recommendations of search terms and/or links would be most appreciated.

 

[boblarson]

No need for VBA, you need to use something like this in your calculated field:

MyFieldNameHere:IIf(Nz([MyDivisorField],0)=0,0,[MyOtherField]/[MyDivisorField])

 

[dayna]

Super Bob Saves the Day!

Works like a charm! Thanks so much for a VBA-free solution, and for allowing me to sleep a little better at night.

 

[boblarson]

Glad we could help

Good luck with the rest of your project.