Problem with "OR" in Edit Record Selection formula

[mdl2]

I am trying to get records where an insurance expiry date has expired based on a parameter date or when insurance is required but no data has been entered in the insurance date field.

( {?date_expire} > {CONTRACT.CGL_INS} or
{?date_expire} > {CONTRACT.AUTO_LIA_INS} or
{?date_expire} > {CONTRACT.PERFORM_BOND} or
( ( isnull({CONTRACT.CGL_INS} ) and CONTRACT.CGL_INS_RQD} = 1 ) or
( isnull({CONTRACT.AUTO_LIA_INS} ) and {CONTRACT.AUTO_LIA_INS_RQD} = 1) or
( isnull({CONTRACT.BUILDER_INS} ) and {CONTRACT.BUILDER_INS_RQD} = 1 ) or
( isnull({CONTRACT.LM_BONDING} ) and {CONTRACT.LM_BONDING_RQD} = 1 ) or
( isnull({CONTRACT.PERFORM_BOND} ) and {CONTRACT.PERFORM_BOND_RQD} = 1) ) )

I am not picking up the records where the expiry dates are null but it the rqd = 1. If I comment out the ?expiry dates
then I pick up these records.

Any help would be appreciated.

 

[lbass]

The first thing I'd try is reordering the formula so that the "isnull" section is first:

(
(isnull({CONTRACT.CGL_INS}) and CONTRACT.CGL_INS_RQD} = 1) or
(isnull({CONTRACT.AUTO_LIA_INS}) and {CONTRACT.AUTO_LIA_INS_RQD} = 1) or
(isnull({CONTRACT.BUILDER_INS}) and {CONTRACT.BUILDER_INS_RQD} = 1) or
(isnull({CONTRACT.LM_BONDING}) and {CONTRACT.LM_BONDING_RQD} = 1) or
(isnull({CONTRACT.PERFORM_BOND}) and {CONTRACT.PERFORM_BOND_RQD} = 1)
) or
( {?date_expire} > {CONTRACT.CGL_INS} or
{?date_expire} > {CONTRACT.AUTO_LIA_INS} or
{?date_expire} > {CONTRACT.PERFORM_BOND} )

-LB

 

[mdl2]

Thanks,

That seemed to have picked up the record that I know was missing. Do you happen to know why ( how does crystal process the record selection)?

 

[lbass]

For whatever reasons, nulls must always be evaluated first or they are ignored. I'm not exactly sure why--it's just a rule to remember and use when creating formulas.

-LB

 

[GMcNamara]

Your first line reads: {?date_expire} > {CONTRACT.CGL_INS}. Crystal is comparing {CONTRACT.CGL_INS} with the parameter value. If {CONTRACT.CGL_INS} is null, is that greater than, less than, or equal to the parameter value? Essentially, this is like comparing two totally different types. Is 1 > null? To our database, the answer is not really yes or no.
If you have two fields in the database which both contain null values, what would return from the following boolean expression (TABLE.fieldA = TABLE.fieldB)? The point is, comparing a known value with a null value will not evaluate correctly and should never be used in your report if possible. You eliminate this possibility by doing null checks first.