Recent versions of Crystal allow you to automatically “Convert Nulls” in specific formulas. The setting is on the formula editor tool bar as a drop down.  You can change “Exceptions for Nulls” to “Convert Null Values to Default values”. Crystal then automatically replaces any null values encountered in the formula with an empty string [""]. This prevents your formulas from returning a blank whenever they encounter a null value.

This works great for formula fields, but not so great in the record selection formula. This problem is that this setting doesn’t get converted into SQL.

Let me give a simple example from the Xtreme sample data. There are 10 records in the customer table that have a null value for Postal Code. If I write a formula field that says:

if IsNull ({Customer.Postal Code})
then "Unknown"
else {Customer.Postal Code}

It will show TRUE for these 10 records. I could also write a formula that says:

if {Customer.Postal Code} = ""
then "Unknown"
else {Customer.Postal Code}

These 10 records will show blank at first, but if I set that formula to “Convert Null to Default”. Then it will also show TRUE for those 10 records. Now lets say I use this as my selection formula:

IsNull ({Customer.Postal Code})

That will pass to the WHERE clause as:

`Customer`.`Postal Code` IS NULL

and it will select those 10 records. But, if I write the selection formula like this:

{Customer.Postal Code} = ""

Even if I tell the selection formula to “Convert Null to Default”, the WHERE clause will be:

`Customer`.`Postal Code` = ''

There are no records that meet that criteria and so no records will be selected.  The key here is that using “Convert Null to Default” works within Crystal, but is not translated to SQL. That is why I avoid using this setting in the record selection formula.