Convert #Error to 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I am doing some calculations in a field that have some zeros and I get
#error since I can not divide by zero (I know this too). Is there a way that
I can convert #Error to a zero?

Eric

SELECT BOL.Plant, BOL.Year, BOL.Errors, BOL.Loaded, IIf([Errors]/[Loaded] Is
Null,0,[Errors]/[Loaded]) AS ErrorsPer100Loaded
FROM BOL;
 
Hi Eric,

Assuming that [Loaded] is your denominator, you could try

IIf([Loaded] > 0,[Errors]/[Loaded],0)

The divide by zero error will be caused by a null or 0 value denominator, so
that's the only one you need to test.


JR
 
Eric,

To cater to all possibilities, something like this...
IIf([Errors] Is Null Or [Loaded] Is Null Or
[Loaded]=0,0,[Errors]/[Loaded])

.... or...
IIf(Nz([Loaded],0)=0,0,Nz([Errors],0)/[Loaded])
 
SELECT BOL.Plant, BOL.Year, BOL.Errors, BOL.Loaded,
IIf( Nz([Loaded],0) = 0, 0, Nz([Errors], 0) / [Loaded] ) AS
ErrorsPer100Loaded
FROM BOL;
 
Van said:
SELECT BOL.Plant, BOL.Year, BOL.Errors, BOL.Loaded,
IIf( Nz([Loaded],0) = 0, 0, Nz([Errors], 0) / [Loaded] ) AS
ErrorsPer100Loaded
FROM BOL;
Click to expand...

The problem is that Access ALWAYS compute BOTW parts of the IIf branch, so you cant divide by zero inside it if you want to avoid errors.

In the Immediate window, try:

? IIf(1 > 2, 1/0, 2)

error even if it should print '2'

hth
PerL
 
Per Larsen said:
Van said:
SELECT BOL.Plant, BOL.Year, BOL.Errors, BOL.Loaded,
IIf( Nz([Loaded],0) = 0, 0, Nz([Errors], 0) / [Loaded] ) AS
ErrorsPer100Loaded
FROM BOL;
Click to expand...

The problem is that Access ALWAYS compute BOTW parts of the IIf branch, so
you cant divide by zero inside it if you want to avoid errors.

In the Immediate window, try:

? IIf(1 > 2, 1/0, 2)

error even if it should print '2'
Click to expand...

While what you're saying is true within VBA, the IIf statement Van's
suggesting works as advertised in queries.
 
Just as a matter of interest I did test IIf([Loaded] >
0,[Errors]/[Loaded],0) with NULL values in *both* the numerator and
denominator and it still evaluated correctly. My thinking was that even if
[Loaded] was NULL the test for > 0 evaluates to false.

:o)

JR

JR said:
Hi Eric,

Assuming that [Loaded] is your denominator, you could try

IIf([Loaded] > 0,[Errors]/[Loaded],0)

The divide by zero error will be caused by a null or 0 value denominator,
so that's the only one you need to test.


JR

Eric said:
Hi everyone,

I am doing some calculations in a field that have some zeros and I get
#error since I can not divide by zero (I know this too). Is there a way
that
I can convert #Error to a zero?

Eric

SELECT BOL.Plant, BOL.Year, BOL.Errors, BOL.Loaded, IIf([Errors]/[Loaded]
Is
Null,0,[Errors]/[Loaded]) AS ErrorsPer100Loaded
FROM BOL;
Click to expand...
Click to expand...
 
Correct. For any Boolean expression:

A {Boolean operator} B

will evaluate to Null if either A is Null or B is Null. If there is no
further processing, this will be considered as not True, i.e. the same as
False.
 
Thanks Van.


JR

Van T. Dinh said:
Correct. For any Boolean expression:

A {Boolean operator} B

will evaluate to Null if either A is Null or B is Null. If there is no
further processing, this will be considered as not True, i.e. the same as
False.

--
HTH
Van T. Dinh
MVP (Access)




JR said:
Just as a matter of interest I did test IIf([Loaded] >
0,[Errors]/[Loaded],0) with NULL values in *both* the numerator and
denominator and it still evaluated correctly. My thinking was that even if
[Loaded] was NULL the test for > 0 evaluates to false.

:o)

JR
Click to expand...
Click to expand...
 
Back
Top