Dividing by Zero in Access Reports

  • Thread starter Thread starter Mark H
  • Start date Start date
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.
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
Click to expand...
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
Click to expand...
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
Click to expand...
Click to expand...

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.
 
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.
Click to expand...

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

Test for 0.
Try something like this:

=IIf([Field2] = 0,"",[Field1]/[Field2])
Click to expand...
Click to expand...

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.
 
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])
Click to expand...
Click to expand...

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.
Click to expand...
 
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])
Click to expand...
Click to expand...

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.
Click to expand...
 
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
'====================================================
 
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
'====================================================
 
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)
Click to expand...
Click to expand...
 
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)
Click to expand...
Click to expand...
Click to expand...
 
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
 
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])
Click to expand...
Click to expand...
 
Back
Top