Selecting Blank Values

It can be frustrating trying to create Crystal Reports showing only records where a particular field is blank - or showing only records where the field is not blank. It can be even more distressing when Crystal gags on a formula at runtime because it's trying to do math (ESPECIALLY date math) on blank values.

You may have discovered the Crystal PrintState function IsNull, and worked it into your selection or formula, expecting all your troubles to be solved, and been confounded by the result (or lack of result). What's going on?

It could be the Crystal Report Option Convert NULL field to default. To find it, open your File menu, then select Report Options - it will be the first item on the left, and it will probably be unchecked.* This item controls the way Crystal treats blank values:

Converting Null Checked Unchecked
Blank Treated As Blank Treated As
Data Type
Date 0/0/0000 Null
Number 0 Null
String "" Null

Naturally, Crystal will find or select different records depending on whether it's converting NULLs or not. For example, say you had employee records containing the following data:

Name BirthDate Salary Marital Status
Tee, Billie 12/31/1963 D
McPhail, Christine 50097.56 M
Feffer, Helen P. 25487.96 S
Morris, Morris W. 2/11/1969 S
Gerald, Bob P. 9/20/1954 21640.04
Snyder, Jim M. 7/22/1970 33371.24

A Crystal report with a selection formula will display different information depending on its Convert NULL setting:

Convert
NULL
Setting Selection Formula Records Displayed
Convert Off IsNull({EMPLOYEE.BIRTH_DATE}) 2 (Mcphail, Feffer)
Convert On IsNull({EMPLOYEE.BIRTH_DATE}) 0

Convert Off IsNull({EMPLOYEE.ANNUAL_SAL}) 2 (Tee, Morris)
Convert On IsNull({EMPLOYEE.ANNUAL_SAL}) 0

Convert Off IsNull({EMPLOYEE.MARITAL}) 2 (Gerald, Snyder)
Convert On IsNull({EMPLOYEE.MARITAL}) 0

*You can set the Default for Convert Null to Default by accessing your File|Options menu, Reporting tab.

Howard Hammerman, Ph.D.
Hammerman Associates, Inc.
http://www.hammerman.com
800-783-2269
Hammerman Associates, Inc. provide Crystal Reports training,
consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments.