Suppressing #Num! when dividing by zero

[Spinbud]

Hello,

I am several calculations where the divisor is zero and the following error
appears: #Num!. How do I make the result zero or blank so this does not
appear.

Many thanks.

 

[Rob Parker]

A simple way is to test if the divisor is 0. Use an expression something
like this:

=Iif(D=0,"",N/D)

This will set the result to a zero-length string. You should not show a 0
instead, as that is NOT the mathematical result. Another possibility which
I sometimes use is to set the result to "n.a." (not available).

HTH,

Rob

 

[Spinbud]

Thanks, I am not sure how to do what you have suggested. In the control
source for my calculation is

=([text603]/[text265])

where the [text265] is a zero amount and I get the #Num!. Is this where I
put the Iif command as you suggest?

Many thanks.

 

[John Spencer]

Try the following expression as a control source.

=IIF([Text265]=0,Null,[text603]/IIF([Text265]=0,1,[Text265]))

If you are doing this in a query you can use the simpler expression
IIF([Text265]=0,Null,[Text265])

The reason for the more complicated expression for a control source is that
Access will evaluate both the true and the false part of the expression and if
either generates an error will return an error. So you can't simply place the
expression x/y in the false part of the expression. It will still display an
error if y is zero.

If you prefer, you can replace the Null in both expressions with a string
value such as "N/A". That will probably cause the expression to return a
string even when it returns the calculation - the returned string will consist
of number characters, but it will still be a string and you will have trouble
manipulating it for any other arithmetic operations.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, I am not sure how to do what you have suggested. In the control
source for my calculation is

=([text603]/[text265])

where the [text265] is a zero amount and I get the #Num!. Is this where I
put the Iif command as you suggest?

Many thanks.

A simple way is to test if the divisor is 0. Use an expression something
like this:

=Iif(D=0,"",N/D)

This will set the result to a zero-length string. You should not show a 0
instead, as that is NOT the mathematical result. Another possibility which
I sometimes use is to set the result to "n.a." (not available).

HTH,

Rob

 

[Spinbud]

Thanks. It worked very well.

Try the following expression as a control source.

=IIF([Text265]=0,Null,[text603]/IIF([Text265]=0,1,[Text265]))

If you are doing this in a query you can use the simpler expression
IIF([Text265]=0,Null,[Text265])

The reason for the more complicated expression for a control source is that
Access will evaluate both the true and the false part of the expression and if
either generates an error will return an error. So you can't simply place the
expression x/y in the false part of the expression. It will still display an
error if y is zero.

If you prefer, you can replace the Null in both expressions with a string
value such as "N/A". That will probably cause the expression to return a
string even when it returns the calculation - the returned string will consist
of number characters, but it will still be a string and you will have trouble
manipulating it for any other arithmetic operations.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, I am not sure how to do what you have suggested. In the control
source for my calculation is

=([text603]/[text265])

where the [text265] is a zero amount and I get the #Num!. Is this where I
put the Iif command as you suggest?

Many thanks.

A simple way is to test if the divisor is 0. Use an expression something
like this:

=Iif(D=0,"",N/D)

This will set the result to a zero-length string. You should not show a 0
instead, as that is NOT the mathematical result. Another possibility which
I sometimes use is to set the result to "n.a." (not available).

HTH,

Rob

Hello,

I am several calculations where the divisor is zero and the following
error
appears: #Num!. How do I make the result zero or blank so this does not
appear.

Many thanks.