More than one IIF statement in query

[tMitch]

Hi -

What is the code that I should use to have more than one IIF statement in a query - in the Expression Builder? AND doesn't work.

I am trying to add 3 columns, but some columns have a null value and some don't, so I am saying IIF IsNull([Column1]), then add other 2 columns, otherwise add all 3 columns. I need to do this for the other 2 columns.

Or is there a better way to add multiple columns where some have Null value?

Thanks in advance!

 

[Newman]

IIf(condition#1,IIf(condition#2,do this if condition#2 is true,do this if condition#2 is false),do this if condition#1 is false)

 

[Jon K]

It is:-
IIF(IsNull([Column1]), [Column2]+[Column3], [Column1]+[Column2]+[Column3]))

If there are Null values in column2 and column3, too, you need to use the nz() functions as well.


Since nz() will change any null values to zero, you can simply use:-
nz([Column1],0) + nz([Column2],0) + nz([Column3],0)

 

[tMitch]

Thanks for replying. The nz() changing values to 0 worked - exactly what I needed.