平特五不中

Basic solution

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"))
Back to top