Hiding a #Num! occurrence in a report output

  • Thread starter Thread starter Stephen Brown
  • Start date Start date
S

Stephen Brown

I have a report that calculates a series of percentages based on the report
calculated.

Sometimes, however this results in a divided by zero calculation which then
appears in the report as #Num!

Is there an simple way of making the result box appear empty is there is a
division by zero result or having the text "N/A" appear instead?

Thanks
 
Stephen,
You can use the NZ function in your calculation, to prevent Null
values from interfering with the calculation.
Or... use an IIF statement in the ControlSource of your control to
determine if an element of the calculation will cause the error, or not.

IFF([Y] = 0, "N/A", [X] / [Y])
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
I'm sure what Al meant to use was IIf() not IFF() ;-)
=IIf([Y] = 0, "N/A", [X] / [Y])

--
Duane Hookom
Microsoft Access MVP


Al Campagna said:
Stephen,
You can use the NZ function in your calculation, to prevent Null
values from interfering with the calculation.
Or... use an IIF statement in the ControlSource of your control to
determine if an element of the calculation will cause the error, or not.

IFF([Y] = 0, "N/A", [X] / [Y])
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Stephen Brown said:
I have a report that calculates a series of percentages based on the report
calculated.

Sometimes, however this results in a divided by zero calculation which
then
appears in the report as #Num!

Is there an simple way of making the result box appear empty is there is a
division by zero result or having the text "N/A" appear instead?

Thanks
Click to expand...


.
Click to expand...
 
Thanks Duane... a typo.
"IIF" only I had my intellesense... :-D
Al Campagna

Duane Hookom said:
I'm sure what Al meant to use was IIf() not IFF() ;-)
=IIf([Y] = 0, "N/A", [X] / [Y])

--
Duane Hookom
Microsoft Access MVP


Al Campagna said:
Stephen,
You can use the NZ function in your calculation, to prevent Null
values from interfering with the calculation.
Or... use an IIF statement in the ControlSource of your control to
determine if an element of the calculation will cause the error, or not.

IFF([Y] = 0, "N/A", [X] / [Y])
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Stephen Brown said:
I have a report that calculates a series of percentages based on the
report
calculated.

Sometimes, however this results in a divided by zero calculation which
then
appears in the report as #Num!

Is there an simple way of making the result box appear empty is there
is a
division by zero result or having the text "N/A" appear instead?

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Many thanks - that worked fine.

Stephen

Al Campagna said:
Thanks Duane... a typo.
"IIF" only I had my intellesense... :-D
Al Campagna

Duane Hookom said:
I'm sure what Al meant to use was IIf() not IFF() ;-)
=IIf([Y] = 0, "N/A", [X] / [Y])

--
Duane Hookom
Microsoft Access MVP


Al Campagna said:
Stephen,
You can use the NZ function in your calculation, to prevent Null
values from interfering with the calculation.
Or... use an IIF statement in the ControlSource of your control to
determine if an element of the calculation will cause the error, or not.

IFF([Y] = 0, "N/A", [X] / [Y])
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have a report that calculates a series of percentages based on the
report
calculated.

Sometimes, however this results in a divided by zero calculation which
then
appears in the report as #Num!

Is there an simple way of making the result box appear empty is there
is a
division by zero result or having the text "N/A" appear instead?

Thanks





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top