Dividing by Zero in Access Reports

M

Mark H

I am trying to divide two columns and get a percentage and the following
errors:

#Div/0! (division by Zero error)
#Num! (0 divide by 0 error)

how do i Make these errors not show on the report.
 
F

fredg

I am trying to divide two columns and get a percentage and the following
errors:

#Div/0! (division by Zero error)
#Num! (0 divide by 0 error)

how do i Make these errors not show on the report.

Would have been nice if you gave us your exact expression.....

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
 
M

Mark H

Below is my exact expression:

=(([Ext Price Compare 1])/([Ext Price Compare 2]))-1

Ext Price Compare 1 is a column and the Ext Price Compare 2 is a column.
They represents sales for a giving time period. There are sometimes Zero's
in Ext Price Compare 1 and sometimes there are zero's in Ext Price Compare 2,
so for example:
Ext Price Compare 1 Ext Price Compare 2
Formula column
146,000 0
#div/0!
0 0
#num!


Thanks in advance,
Mark

fredg said:
I am trying to divide two columns and get a percentage and the following
errors:

#Div/0! (division by Zero error)
#Num! (0 divide by 0 error)

how do i Make these errors not show on the report.

Would have been nice if you gave us your exact expression.....

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
 
F

fredg

Below is my exact expression:

=(([Ext Price Compare 1])/([Ext Price Compare 2]))-1

Ext Price Compare 1 is a column and the Ext Price Compare 2 is a column.
They represents sales for a giving time period. There are sometimes Zero's
in Ext Price Compare 1 and sometimes there are zero's in Ext Price Compare 2,
so for example:
Ext Price Compare 1 Ext Price Compare 2
Formula column
146,000 0
#div/0!
0 0
#num!

Thanks in advance,
Mark

fredg said:
I am trying to divide two columns and get a percentage and the following
errors:

#Div/0! (division by Zero error)
#Num! (0 divide by 0 error)

how do i Make these errors not show on the report.

Would have been nice if you gave us your exact expression.....

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])

Try:

=IIf(Nz([Ext Price Compare 1],0)=0 or Nz([Ext Price Compare
2]),0)=0,"",([Ext Price Compare 1]/[Ext Price Compare 2])-1)

The Nz() function tests whether the field is Null. If so, it replaces
the Null with a 0 in this calculation. If either field is =0 then no
division is done. A blank is displayed in the report control.

Look up the Nz() function in VBA help.
 
D

Duane Hookom

I don't care for IIf() or any other expressions that could return two
different data types. I would replace the "" with Null.

=IIf(Nz([Ext Price Compare 1],0)=0 or Nz([Ext Price Compare
2]),0)=0,Null,([Ext Price Compare 1]/[Ext Price Compare 2])-1)

--
Duane Hookom
Microsoft Access MVP


fredg said:
Below is my exact expression:

=(([Ext Price Compare 1])/([Ext Price Compare 2]))-1

Ext Price Compare 1 is a column and the Ext Price Compare 2 is a column.
They represents sales for a giving time period. There are sometimes Zero's
in Ext Price Compare 1 and sometimes there are zero's in Ext Price Compare 2,
so for example:
Ext Price Compare 1 Ext Price Compare 2
Formula column
146,000 0
#div/0!
0 0
#num!

Thanks in advance,
Mark

fredg said:
On Wed, 13 May 2009 12:09:04 -0700, Mark H wrote:

I am trying to divide two columns and get a percentage and the following
errors:

#Div/0! (division by Zero error)
#Num! (0 divide by 0 error)

how do i Make these errors not show on the report.

Would have been nice if you gave us your exact expression.....

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])

Try:

=IIf(Nz([Ext Price Compare 1],0)=0 or Nz([Ext Price Compare
2]),0)=0,"",([Ext Price Compare 1]/[Ext Price Compare 2])-1)

The Nz() function tests whether the field is Null. If so, it replaces
the Null with a 0 in this calculation. If either field is =0 then no
division is done. A blank is displayed in the report control.

Look up the Nz() function in VBA help.
 
J

John Spencer

I think that may still error. Since I believe that when you use IIF as
an expression in a control (as opposed to using it in a query) both the
True and the False argument are evaluated. So you might have to use
something along the lines of the following to keep the division from
generating an error.

=IIf(Nz([Ext Price Compare 1],0)=0
or Nz([Ext Price Compare 2],0)=0
, Null
,([Ext Price Compare 1]/
IIF([Ext Price Compare 2]=0,1,[Ext Price Compare 2))-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

Kay Starnes

Hi John,
I tried to use this information and it gives me a #num! message if there is
zero's in the [proc1] field and the [rtn 1] field.

I want it to show 0.00% if [rtn 1] = 0 and [proc1]=0

Here is your description with my fields inserted.
=IIf((Nz([rtn 1],0)=0 Or Nz([proc1]),0)=0,Null,([rtn 1]/Nz([proc1],0)=0,0))

Will you give it a shot? Thanks, K
John Spencer said:
I think that may still error. Since I believe that when you use IIF as
an expression in a control (as opposed to using it in a query) both the
True and the False argument are evaluated. So you might have to use
something along the lines of the following to keep the division from
generating an error.

=IIf(Nz([Ext Price Compare 1],0)=0
or Nz([Ext Price Compare 2],0)=0
, Null
,([Ext Price Compare 1]/
IIF([Ext Price Compare 2]=0,1,[Ext Price Compare 2))-1)

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


Duane said:
I don't care for IIf() or any other expressions that could return two
different data types. I would replace the "" with Null.

=IIf(Nz([Ext Price Compare 1],0)=0 or Nz([Ext Price Compare
2]),0)=0,Null,([Ext Price Compare 1]/[Ext Price Compare 2])-1)
 
D

Duane Hookom

I'm not sure what you want to display but you have either add or missed
something and have ()s in the wrong places.

Try this:

=IIf((Nz([rtn 1],0)=0 Or Nz([proc1],0)=0,Null,[rtn 1]/[proc1])


--
Duane Hookom
Microsoft Access MVP


Kay Starnes said:
Hi John,
I tried to use this information and it gives me a #num! message if there is
zero's in the [proc1] field and the [rtn 1] field.

I want it to show 0.00% if [rtn 1] = 0 and [proc1]=0

Here is your description with my fields inserted.
=IIf((Nz([rtn 1],0)=0 Or Nz([proc1]),0)=0,Null,([rtn 1]/Nz([proc1],0)=0,0))

Will you give it a shot? Thanks, K
John Spencer said:
I think that may still error. Since I believe that when you use IIF as
an expression in a control (as opposed to using it in a query) both the
True and the False argument are evaluated. So you might have to use
something along the lines of the following to keep the division from
generating an error.

=IIf(Nz([Ext Price Compare 1],0)=0
or Nz([Ext Price Compare 2],0)=0
, Null
,([Ext Price Compare 1]/
IIF([Ext Price Compare 2]=0,1,[Ext Price Compare 2))-1)

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


Duane said:
I don't care for IIf() or any other expressions that could return two
different data types. I would replace the "" with Null.

=IIf(Nz([Ext Price Compare 1],0)=0 or Nz([Ext Price Compare
2]),0)=0,Null,([Ext Price Compare 1]/[Ext Price Compare 2])-1)
 
J

John Spencer

I think you will need to use

=IIf((Nz([rtn 1],0)=0 Or Nz([proc1]),0)=0,0,[rtn 1]/IIF([proc1]=0,1,[Proc1]))

Life would be a lot simpler if you could do the calculation in the query.
Queries handle IIF slightly different than VBA. So the expression in a
calculated field in query could be as follows:

IIf(Nz([proc1],0)=0,0,Nz([rtn 1],0)/[Proc1])

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

Kay Starnes

John you are the best! This worked so nicely. I wish I could do the
calculation on the sub report but is it using data on the main report for the
calculation.

John Spencer said:
I think you will need to use

=IIf((Nz([rtn 1],0)=0 Or Nz([proc1]),0)=0,0,[rtn 1]/IIF([proc1]=0,1,[Proc1]))

Life would be a lot simpler if you could do the calculation in the query.
Queries handle IIF slightly different than VBA. So the expression in a
calculated field in query could be as follows:

IIf(Nz([proc1],0)=0,0,Nz([rtn 1],0)/[Proc1])

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

Duane said:
I'm not sure what you want to display but you have either add or missed
something and have ()s in the wrong places.

Try this:

=IIf((Nz([rtn 1],0)=0 Or Nz([proc1],0)=0,Null,[rtn 1]/[proc1])
 

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