Crystal Reports – If field is empty select another field

by

The following guide describes how to create a Crystal Report which check if a field is blank (NULL or empty) and if it is use an alternative field.

This could be helpful if your database table has two fields for similar information and there are inconsistencies with the data entry – for example if there is a street address POST CODE and a postal address POST CODE however only one will contain data.

In this example the database field containing the post codes will be ‘Postal_Address_PostCode’ and ‘Street_Address_PostCode’. We will be using the ‘SQL Expression Fields’ feature to query the database.

Please note, this guide was created for Crystal Reports 2008 (version 12), however the same process should work with other versions of the Crystal Reports application.

 

  1. Open the report in Crystal Reports
  2. Using the Field Explorer, right-click on ‘SQL Expression Fields’ and select ‘New’
  3. Crystal2008-SelectPostcode1
  4. Enter in the name for the field, for example SelectPostcode
  5. Click ‘OK’
  6. In the formula field enter in the following code (replacing the database values with your own)
  7. {fn IFNULL("databaseTable"."Postal_Address_PostCode", "databaseTable"."Street_Address_PostCode")}
  8. Crystal2008-SelectPostcode2

  9. Click ‘Save and close’ to save the changes

  10. Insert the SelectPostcode field into the report, format and create a heading as required
  11. Crystal2008-SelectPostcode3
  12. Preview or run the report. The report will now list either the street or postal post code for each record. If the street post code database field is empty it will use the postal post code.
  13. Crystal2008-SelectPostcode4