#Error when dividing by zero

[Joe]

I have a query where I've returned two values: Total # HR Review and
then # HR Review. I then have a field where I'm figuring a percent
with the formula: FormatPercent([# HR Review]/[Total # HR Review],0)
My problem is that when there is a 0 in both fields, then the result =
#Error. How do I write the formula so that it returns 0 instead of
#Error?

 

[Bob Barrows]

I have a query where I've returned two values: Total # HR Review and
then # HR Review. I then have a field where I'm figuring a percent
with the formula: FormatPercent([# HR Review]/[Total # HR Review],0)
My problem is that when there is a 0 in both fields, then the result =
#Error. How do I write the formula so that it returns 0 instead of
#Error?

Iif() to the rescue.

FormatPercent(
IIF([Total # HR Review]<>0,[# HR Review]/[Total # HR Review],0)
,0)

 

[Krzysztof Naworyta]

Bob Barrows wrote:
| Joe wrote:
|| I have a query where I've returned two values: Total # HR Review and
|| then # HR Review. I then have a field where I'm figuring a percent
|| with the formula: FormatPercent([# HR Review]/[Total # HR Review],0)
|| My problem is that when there is a 0 in both fields, then the result
|| = #Error. How do I write the formula so that it returns 0 instead of
|| #Error?
|
| Iif() to the rescue.
|
| FormatPercent(
| IIF([Total # HR Review]<>0,[# HR Review]/[Total # HR Review],0)
| ,0)

I think it is better to convert 0 to Null:

? [# HR Review]/iif([Total # HR Review]=0,Null,[Total # HR Review])

or

? [# HR Review]/NullIf([Total # HR Review],0)

where
Function NullIf(arg1, arg2)
If arg1 = arg2 then
NullIf = Null
Else
NullIf = arg1
End If
End Function