I'm having trouble seeing the error in this expression

[robertagc]

I'm trying to write an expression that circumvents a divide by zero error. Expr4 and 5 may or may be Null, Zero or an integer greater than 0. I want to evaluate Expr4 and 5 together in Expr2. I've been trying to work with some version of the following all day, but Access keeps defaulting to the original expression I had stored.

The original expression reads as follows:


Expr2:iif([datum1]+[datum2]+[datum3]+[datum4]=0,0,IIf([datum3] Is Null,[datum1]/[datum2],IIf([datum1] Is Null,[datum3]/[datum4],([datum3]+[datum1])/([datum4]+[datum2]))))

This one works, but I get an error if Datum 1 or 3 is Null and the other Datum equal 0. I tried fudging around with this expression to massage-out the error, and it just became impossible to manage. When I tried to do was create two new expression to manage datum 1 and 2 together and datum 3 and 4 together. I figured I could condense the work of those two expressions into a new Expr 2 as follows:

Expr2: iif(([Expr4] is null) AND ([Expr5] is null),Null,(iif(([Expr4] is null) AND ([Expr5] is not null),[Expr5],(iif(([Expr5] is null) AND ([Expr4] is not null),[Expr4],(iif(([Expr4]+[Expr5])=0,0,(([Expr4]+[Expr5]/2)))))

When I put this into the expression builder or copy the expression directly into the query design, Access reverts back to the original expression. There must be something wrong with it, and it's probably breathtakingly simple, but I don't see it. Would somebody mind seeing what I am clearly incapable of seeing?

Thanks,


-Rob

[June7]

Simple arithemetic with Null should result in Null.

What is the exact error on the original expression?

Too many parens in the second. Think might need IsNull instead of Is Null. Review http://allenbrowne.com/casu-12.html

Expr2: IIf(IsNull([Expr4]) AND IsNull([Expr5]), Null, IIf(IsNull([Expr4]) AND Not IsNull([Expr5]), [Expr5], IIf(IsNull([Expr5]) AND Not IsNull([Expr4]), [Expr4], IIf([Expr4]+[Expr5]=0,0, [Expr4]+[Expr5]/2))))

Do you want to divide Expr5 by 2 or the sum of Expr4 and Expr5 by 2?

[qa12dx]

I'm trying to write an expression that circumvents a divide by zero error.

i think u answered your own q. but in ur expr u didn't.

try;
iif([datum1]+[datum2]+[datum3]+[datum4]=0,0,
IIf([datum3] Is Null and [datum2]>0,[datum1]/[datum2],
IIf([datum1] Is Null and [datum4]>0,[datum3]/[datum4],
([datum3]+[datum1])/([datum4]+[datum2]))))

best is to not work with nulls. if in your table set the default value of the datum fields to zero. for the existing records run an update qry to set them to 0. when you don't have to mess with nulls in calcs, running calcs become infinitly easier...

if u run append queries and the fields get set as null, run update queries to set to 0 before running the calc qrys.

[robertagc]

June, that was perfect! Thanks. Also, yes, I want to divide the sum of 4 and 5 by 2, not 5 by 2. I was missing a parenthetical set.

qa12dx, I agree. Working with Nulls is a pain. When I build dbs for my own use, I avoid them. However, this was for work, and the situation is such where Nulls are just part of the job.

Thanks again!
-Rob