Calculations in report footer

[Tips]

I have created a report, based on a query to filter results by date range as user selected.
the report footer is performing calculations (percentages) from the values returned.
Sometimes some of these fields might be returned blank and when this happens my calculations return #num! or #div/0!
How can i get these to display a 0 instead of the errors?

Thanks in advance.

 

[vbaInet]

You can use the IsError() function like this:

IIF(IsError([COLOR=Red]"Your calculation"[/COLOR]), 0, [COLOR=Red]"Your calculation"[/COLOR])

... but before you do, can you tell us exactly what instance the error occurs and what the calculation is.

 

[Tips]

The calculation is very simple "=([text39]/[text24])" both of these fields are also calculated fields in the report footer from values returned via the query. if these fields are actual values no problem, but as soon as one of the is a blank or 0 then i get the error.

 

[Tips]

The second formula i get this on is "=([text20]/[text22]/[text35])"

 

[vbaInet]

You're getting a division by zero error.

And your second formula needs to be bracketed properly. The following will yield a different result from yours: =[text20]/([text22]/[text35]) So you need to place the parentheses in the right place and order.

To solve your div by 0 problem, do the following:

=IIF([text24] = 0, Null, [text39]/[text24])

Finally names like text20 and text35 are meaningless. I would advise you give that meaningful names without spaced and special charaters which are:

~@#$%^&*_-+=\}{"';:?/><,.![]|

 

[Tips]

Thanks for your advise and input.
Have done as suggested and working great.

Thanks again.

 

[vbaInet]

You're welcome.

And welcome to the forum by the way.