Strange behavior with IIF

Saturday 15 December 2012 @ 12:42 am

I found an interesting behavior the other day.  A customer had written a formula that was hitting null values and failing.  Normally I would do something like this:

If  IsNull ({FieldA}) then {FieldB} else {FieldA}

But this customer had written it using IIF like this:

IIF (IsNull ({FieldA}) ,{FieldB} , {FieldA})

I expected it to do the same thing and was surprised to find that IIF doesn’t work the same as IF THEN.  With some testing I found that it works fine with literal values like this

IIF (IsNull ({FieldA}) ,"X" , "Y" )

Just not when you use fields as the ‘then’ and ‘else’ values. Of course if you are using a more recent version of CR you can flip the switch at the top of the formula editor from “Exception for Nulls” to “Default value for Nulls”.  The other advantage of this is you can now change the formula to say:

IIF ({FieldA} = "" , {FieldB} , {FieldA})

This will cover both Null values and empty strings. And if you want to also include spaces you can use:

IIF ({FieldA} < "0" , {FieldB} , {FieldA})

Combine this with “Default values for Nulls” and you are catching Null, blank and any number of spaces, all at the same time.

But my preference is still to use IF THEN ELSE. I find it much easier to read when I have to nest or chain several together.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server