Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Business Objects: Crystal Reports 4 Other topics FAQ

Report Development

Selecting Blank Values by HowardHammerman
Posted: 27 Oct 01

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:

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.
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.

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close