Here is a trap in Crystal when it comes to using the selection formula.  Say you have two fields A and B. You want to include all records where either A or B is equal to X. Your selection formula would look like this:

{A} = “X” or {B} = “X”

But what happens if A is null on a record where B is equal to X? Null values cause CR formulas to stop working, so CR would stop processing that formula before it ever got around to looking at B. CR would skip that record. But not if the the formula was reversed:

{B} = “X” or {A} = “X”

This formula should logically return the same result as the first example but it doesn’t.  The record I described above would show up just fine using this second formula, but not using the first. It doesn’t make sense that flipping the formulas around should get you different results. It certainly wouldn’t happen in a straight SQL Query.

Fortunately there is an easy fix. The simplest method is to go into the selection formula and look at the toolbar. There is a setting at the top that defaults to say “Exception for nulls”. Change this to say “Default Value for Nulls” and that should make the formula work correctly either way.

If you don’t have this setting because you have an older version of CR, or if for some other reason this doesn’t work in your environment you can use this:

(( not IsNull ({A}) and {A} = “X” ) or {B} = “X” )

The outer pair of parentheses is to make sure that Crystal doesn’t accidentally reverse the A and B rules. That might happen if you wrote this in the selection formula and then added another rule in the select expert. With the outer parentheses in place CR won’t modify the rules.