Full Version: IIF statement with Null Values
sparkyrose
Hi All,

I'm trying to create a calculated field which will be the average of two other fields in the same query. In some cases one or other (or both) of the fields may be empty. If only one is Null I want the value of the other field to show (i.e. the average of 1 value), and obviously if both are Null then the result is Zero.

I tried

CODE
Average: IIf([2006] Is Not Null And [2007] Is Not Null,(([2006]+[2007])/2),(2006]+[2007]))

I hoped that by adding the two fields in the FALSE part I would end up with the correct value, i.e. Integer + 0 = Integer

Unfortunately, Access doesn't seem to like adding an integer and a zero value and returns an empty cell.

It should only be empty if both 2006 AND 2007 are zero

Any thoughts?

Doug Steele
Use the Nz function:

Average: IIf([2006] Is Not Null And [2007] Is Not Null,(([2006]+[2007])/2),(Nz([2006],0)+Nz([2007],0)))

That will result in 0 if both are Null.
dashiellx2000
You're very close. The one thing you have to understand is that Anything + Null = Null.

So really what you want is:

CODE
Average: IIf(IsNull([2006]) or IsNull([2007]),(Nz([2006],0)+Nz([2007],0)),(([2006]+[2007])/2))

HTH.
sparkyrose
Fantastic, worked perfectly!!

Thanks