Problem:
Incorrect results when a Crystal Reports record selection formula includes an "OR" when some of the fields you are selecting have a null value.
Solution:
Do NOT use the 'Convert NULL field value to default' option as it can lead to further reporting errors.
When writing an "OR" clause in a record selection formula (Selection Formula Editor), Crystal Decisions recommends adding the condition "and not isnull()" to the referenced field.
Example A: Returns 498 rows **Incorrect result**
| {SR_ACADEMIC_FAC.TERM_CODE_KEY}= "200301" and |
| {SR_ACADEMIC_FAC.PRIM_FACULTY_CODE}= "AR" and |
| {SR_ACADEMIC_FAC.REGD_NON_WITHDRAWN_IND}= "Y" and |
| ({SR_ACADEMIC_FAC.PRIM_MAJOR1_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MINOR1_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MAJOR2_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MINOR2_CODE} = "ECN8) |
Example B: Returns 335 rows **Incorrect result**
| {SR_ACADEMIC_FAC.TERM_CODE_KEY}= "200301" and |
| {SR_ACADEMIC_FAC.PRIM_FACULTY_CODE}= "AR" and |
| {SR_ACADEMIC_FAC.REGD_NON_WITHDRAWN_IND}= "Y" and |
| ({SR_ACADEMIC_FAC.PRIM_MAJOR1_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MINOR2_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MAJOR2_CODE} = "ECN8" or |
| {SR_ACADEMIC_FAC.PRIM_MINOR1_CODE} = "ECN8) |
Example C: Returns 574 rows **Correct result**
| {SR_ACADEMIC_FAC.TERM_CODE_KEY}= "200301" and |
| {SR_ACADEMIC_FAC.PRIM_FACULTY_CODE}= "AR" and |
| {SR_ACADEMIC_FAC.REGD_NON_WITHDRAWN_IND}= "Y" and |
| ((not isnull({SR_ACADEMIC_FAC.PRIM_MAJOR1_CODE}) and |
| {SR_ACADEMIC_FAC.PRIM_MAJOR1_CODE} = "ECN8") or |
| (not isnull({SR_ACADEMIC_FAC.PRIM_MINOR1_CODE}) and |
| {SR_ACADEMIC_FAC.PRIM_MINOR1_CODE}="ECN8")) |
| ((not isnull({SR_ACADEMIC_FAC.PRIM_MAJOR2_CODE}) and |
| {SR_ACADEMIC_FAC.PRIM_MAJOR2_CODE} = "ECN8") or |
| (not isnull({SR_ACADEMIC_FAC.PRIM_MINOR2_CODE}) and |
| {SR_ACADEMIC_FAC.PRIM_MINOR2_CODE}="ECN8")) |