How to suppress "#Error" from the query results?

[Guest]

I have a formula that gives me a "#Error" result when the divisor is zero. I
wanted to suppress this error message by building an additional query with
the following formula -
Expr1: IIf(IsError([Price]);0;[Price])

But I still keep geting the "#Error" in the result. What I would like is
where ever the price is "#Error" to give me a 0 (zero) instead.

Could someone advise, thanks.

 

[Allen Browne]

You need to go a step deeper, and prevent the division by zero.

For example:
IIf([Denom]=0, 0, [Num] / [Denom])

 

[Guest]

No Problem!

In my first query I have Qty column and a Value column. So I have an
expression -
Price: a0506_1Q_MD_Bud_06_ConsA![SumOfForecast
Value]/a0506_1Q_MD_Bud_06_ConsA![SumOfForecast Qty SSU]

Unfortunately, in my data set there are instances when both are 0(zero),
hence division by 0 -leads to "#Error".

Hope this is helpful and thanks.

Don't kill the messenger; solve the problem. Under what circumstances are you
accessing the "divisor=0" error?

Sam

I have a formula that gives me a "#Error" result when the divisor is zero. I
wanted to suppress this error message by building an additional query with
the following formula -
Expr1: IIf(IsError([Price]);0;[Price])

But I still keep geting the "#Error" in the result. What I would like is
where ever the price is "#Error" to give me a 0 (zero) instead.

Could someone advise, thanks.

 

[OfficeDev18 via AccessMonster.com]

Don't kill the messenger; solve the problem. Under what circumstances are you
accessing the "divisor=0" error?

Sam

I have a formula that gives me a "#Error" result when the divisor is zero. I
wanted to suppress this error message by building an additional query with
the following formula -
Expr1: IIf(IsError([Price]);0;[Price])

But I still keep geting the "#Error" in the result. What I would like is
where ever the price is "#Error" to give me a 0 (zero) instead.

Could someone advise, thanks.

 

[John Spencer]

So, do as the posters suggested and change the formula to

IIF(a0506_1Q_MD_Bud_06_ConsA![SumOfForecast Qty SSU]=0, 0,
a0506_1Q_MD_Bud_06_ConsA![SumOfForecast
Value]/a0506_1Q_MD_Bud_06_ConsA![SumOfForecast Qty SSU])

If a0506_1Q_MD_Bud_06_ConsA![SumOfForecast Qty SSU] is Zero this returns
Zero. If you want Null instead then replace the last 0 in the first line
with Null.

No Problem!

In my first query I have Qty column and a Value column. So I have an
expression -
Price: a0506_1Q_MD_Bud_06_ConsA![SumOfForecast
Value]/a0506_1Q_MD_Bud_06_ConsA![SumOfForecast Qty SSU]

Unfortunately, in my data set there are instances when both are 0(zero),
hence division by 0 -leads to "#Error".

Hope this is helpful and thanks.

Don't kill the messenger; solve the problem. Under what circumstances are
you
accessing the "divisor=0" error?

Sam

I have a formula that gives me a "#Error" result when the divisor is
zero. I
wanted to suppress this error message by building an additional query
with
the following formula -
Expr1: IIf(IsError([Price]);0;[Price])

But I still keep geting the "#Error" in the result. What I would like is
where ever the price is "#Error" to give me a 0 (zero) instead.

Could someone advise, thanks.