#Error when dividing by zero

J

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?
 
B

Bob Barrows

Joe said:
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)
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top