Crystal Reports : Dealing with (and testing for) NULLS

NULLS are a pain. Because they are functionally equivalent to blanks. A NULL field and a "blank" field return the same thing; nothing. But they are technically different values and as a Crystal Reporter, you will need to know how to deal with them.

Let us consider the field {CONTACT2.USTATUS}, which tracks a customers' Service Status.

For those records with an explicit value (i.e. No Service), this is returned normally as a string.

For those records that HAD a value at some point in the past, but have since been "blanked out", these blank values are also returned normally as a blank string.

For those records where USTATUS has NEVER been written to, this will return a NULL. A NULL is "less than" a blank.

So when you propose the following Selection Criteria on USTATUS like this (selecting for anyone who doesn't have a Status);

{CONTACT2.USTATUS} = ""

This is only half of the battle. We must also test for NULL values as well, like this;

{CONTACT2.USTATUS} = "" AND ISNULL({CONTACT2.USTATUS}) = TRUE

Unlike a SQL statement, which can simply use USTATUS IS NULL, we must use the special ISNULL() Crystal function. This function returns a BOOLEAN, which is either TRUE or FALSE.

Where your particular NULLS spring up will be determined by how well-written your front end software is. For example, they are more often found in so called "legacy" systems than newer, more modern ones.