Get All of the selections 3

[wilekoyote76]

Using this equation I'm getting only partial results. I want each of the conditions to be checked. If statements only check one and move on. How can I rework this selection query so it checks for ALL the conditions and displays ALL the records within the search dates?

if {CONTACT2.UCLMNO} > 0 then
({CONTACT2.UIIDATPAID} >= {?begindate} and {CONTACT2.UIIDATPAID} <= {?enddate}) OR
{CONTACT2.UPYMTDATE} >= {?begindate} and {CONTACT2.UPYMTDATE} <= {?enddate} OR
{CONTACT2.URIDATPAID} >= {?begindate} and {CONTACT2.URIDATPAID} <= {?enddate} OR
{CONTACT2.URODATPAID} >= {?begindate} and {CONTACT2.URODATPAID} <= {?enddate};

 

[lbass]

(
{CONTACT2.UCLMNO} > 0 and
(
(
{CONTACT2.UIIDATPAID} >= {?begindate} and
{CONTACT2.UIIDATPAID} <= {?enddate}
) OR
(
{CONTACT2.UPYMTDATE} >= {?begindate} and
{CONTACT2.UPYMTDATE} <= {?enddate}
) OR
(
{CONTACT2.URIDATPAID} >= {?begindate} and
{CONTACT2.URIDATPAID} <= {?enddate}
) OR
(
{CONTACT2.URODATPAID} >= {?begindate} and
{CONTACT2.URODATPAID} <= {?enddate}
)
)
)

The parens are critical, except the very first and very last could be removed if there are no other selection criteria.

-LB

 

[wilekoyote76]

This only brings up whatever the first statement is...in this case:
{CONTACT2.UIIDATPAID} >= {?begindate} and
{CONTACT2.UIIDATPAID} <= {?enddate}

If I switch it so that the statement above is not the first statement, it uses ONLY the selection criteria for the next "first statement". It doesn't go after any of the other "OR" statements to display that selection.

 

[Turkbear]

Hi,
As long as records exist that meet those criteria
({CONTACT2.UCLMNO} > 0 AND Any of the OR statements))
all that meet the ORs should be returned - double-check your parens to be sure that the OR set is isolated as 1 criteria.

As a test, try adding in each OR clause separately.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Can any of the fields be null?

Please show the actual formula as you implemented it. If the fields cannot be null, then I think you left out critical parens. Note that a particular record will be selected if it meets any of the criteria.

-LB

 

[wilekoyote76]

I actually posted the whole selection formula already. The fields if null, need to be ignored.

Basically this is what I am trying to do. Only one of the fields will have "something" in it within the following fields:
{CONTACT2.UIIDATPAID}
{CONTACT2.UPYMTDATE}
{CONTACT2.URIDATPAID}
{CONTACT2.URODATPAID}

If it has "something" in the field, I need it selected for display to parse out the data.

(

//not really needed but it was for the "if" statement so technically its now useless and is now commented out.
//{CONTACT2.UCLMNO} > 0 and

(
(
//Check if this field has any numbers in it. If it does
//this must be selected for use in the selection and //equations
{CONTACT2.UIIDATPAID} >= {?begindate} and
{CONTACT2.UIIDATPAID} <= {?enddate}
) OR
(
//if the record didn't match the uiidatpaid
//check this selection to see if there was
//a payment date on a "regular" claim

{CONTACT2.UPYMTDATE} >= {?begindate} and
{CONTACT2.UPYMTDATE} <= {?enddate}
) OR
(
//if the upayment and uiidatpaid had no data
//check the reinspect claim paid between the dates
//for any possible numbers

{CONTACT2.URIDATPAID} >= {?begindate} and
{CONTACT2.URIDATPAID} <= {?enddate}
) OR
(
//if the claim needed to be reopened,
//check the reopen date paid on the claim

{CONTACT2.URODATPAID} >= {?begindate} and
{CONTACT2.URODATPAID} <= {?enddate}
)
)
)

 

[IanWaterman]

As LB says If any of the date fields are Null the formula will not evaluate.

Have you tried setting default values for Nulls

File->Report Options

Check - Convert Database Null Values to Default.

Ian

 

[Madawc]

If you don't wnat to remvoe the nulls from the database, you'd need to start with test like

if not isnull({CONTACT2.UIIDATPAID}) and ..

Fill this in for the other values you need to do tests on.

One feature of Crystal is that a formual will stop if it reads a field that is null, even if provision for nulls is made later on. You have to be sure you've allowed for all possible null values before doing any other tests. I found it very confusing when I first came to Crystal from a Mainframe background.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP