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.
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])
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])
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.
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)
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)
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])
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.