Query Calculation

[Daud Akbar]

i have two fields in a Query A and B. I want to calculate
the percentage of both fields in field C of the query i.e
A/B*100. it formula works ok but in records where field B
carries 0 value it gives the ans in #Error in the query
and !Null in report of C column.

I wana get rid of this problem, is there any way to
replace #error or !null into 0 or someting else.

waiting for the reply
thanks.

 

[John Pritchard]

Hi Daud,

If field B is zero you are dividing by zero and the result
is undefined - hence the null values. You can avoid this
by having a where clause that doesn't allow zero :-

select A/B*100
from yourtable
where B <> 0

If you need the rows for some reason then try the
nz function:-

nz(yourvar,0)

if yourvar is not null then nz returns the value of
yourvar, if yourvar is null then nz returns 0 - or
whatever you put in the second parameter.

Good Luck

John P.

 

[Bullschmidt]

Originally posted by Daud Akbar

i have two fields in a Query A and B. I want to calculate
the percentage of both fields in field C of the query i.e
A/B*100. it formula works ok but in records where field B
carries 0 value it gives the ans in #Error in the query
and !Null in report of C column.

I wana get rid of this problem, is there any way to
replace #error or !null into 0 or someting else.

waiting for the reply
thanks.

MyCalcedField: Round(IIf(<>0,[A]/*100,0), 2)

--
J. Paul Schmidt, Freelance Access and ASP Developer
http://www.Bullschmidt.com/Login.asp - Database on the Web Demo
http://www.Bullschmidt.com/Access
Sample Access Invoices Database


Posted via http://dbforums.com

 

[Van T. Dinh]

Try using Immediate IF function like:

IIf(b = 0, 0, a/b*100)

HTH
Van T. Dinh
MVP (Access)

 

[Marty]

-----Original Message-----
i have two fields in a Query A and B. I want to calculate
the percentage of both fields in field C of the query i.e
A/B*100. it formula works ok but in records where field B
carries 0 value it gives the ans in #Error in the query
and !Null in report of C column.

I wana get rid of this problem, is there any way to
replace #error or !null into 0 or someting else.

waiting for the reply
thanks.
.
do this: IIf(B=0,0,A/B*100)