#error in report-easy to answer

[Therat]

I am sure there is a quick fix for this. I am building a report that uses a query. The query performs simple calcuations like percent change. There are a few rows in this query that are dividing by 0, so the result is "#error". What is a simple expression I can write in the report design view so that when I view the report it puts a "-" if the formula result is a "#error"?

Thanks in advance!!

 

[antomack]

Why not have the expression in the query setting the value to null?

iif([DivideBy]=0 or isnull([DivideBy]),0,[Number]/[DivideBy])

This will then give zero result on your report instead of the #error.

 

[Therat]

Not the answer I was looking for

Thanks for attempting an answer, but I don't think that will work. Why? In the report, doesn't the zero turn into a 0%? When in actuality it shouldn't be a 0%. I had previously tried an IIF criteria in the design view of the report, but since "-" is not a number, I get an error message. I don't want to confuse my audience who reads the report (higher ups tend to be mathematically weak) so I need it to return a "-". Am I making sense?

Any more thoughts?!?!
Thanks.

 

[Rich]

iif([DivideBy]=0 or isnull([DivideBy])," ",[Number]/[DivideBy])

 

[Pat Hartman]

iif([DivideBy]=0 or isnull([DivideBy]),null,[Number]/[DivideBy])

You want the result to be null. Zero length strings are only applicable to text fields and using one in this case will force the IIf() to return a string.

 

[antomack]

Set the folowing as the Control Source for your field with the #error on the report and it will put in blanks instead of #error.

=IIf(IsError([YourFieldName]),null,[YourFieldName])