Record Selection Formula

[mdj3366]

I have a database that stores check detail for each person. Each person can have a differing number of checks. Each check is assigned a status (paid, eligible, or ineligible) and a date. Looks like this:

7/01/2003 paid (1974)
7/08/2003 ineligible (2036)
7/14/2003 ineligible

Each person is assigned a status (active, inactive). When the status is inactive, a date is entered. I created a Crystal report, which has a date range parameter, to list those checks which have been paid within that date range. My dilemma is this: when a person’s status is inactive, I need to see all the remaining payments (whose status is now ineligible) regardless of the date of the payment. And if a person is inactive, that person will only be reported once and not in subsequent reports, based upon the inactive date. I have tried to use the following report selection formula, but to no avail:

[{sp_calc_return_comp_sui.paymentStatusID} = 1974 and {sp_calc_return_comp_sui.payment_date}in {?DateRange}
or {sp_calc_return_comp_sui.paymentStatusID}=2036 and {sp_calc_return_comp_sui.returnToWorkDate} in {?DateRange}
or {sp_calc_return_comp_sui.payment_date} in {?DateRange}]

Any help would be greatly appreciated!

 

[synapsevampire]

Try posting real example data (all columns involved) and expected output, your description leaves too much to the imagination.

" My dilemma is this: when a person’s status is inactive, I need to see all the remaining payments (whose status is now ineligible)"

I thought that this was a Check Detail, is it AR? And what is meant by a remaining payment?

Your record selection selects all rows in the date range because of the last line:

or {sp_calc_return_comp_sui.payment_date} in {?DateRange}]

What you probably want to do is an If statement in the record selection formula, but it's too difficult to try to construct from your post.

-k

 

[mdj3366]

synapsevampire,
The report is outputting columns of data, ie.
payment date amount tax status
8/16/2003 500.00 25.00 paid
8/23/2003 500.00 25.00 ineligible
8/30/2003 500.00 25.00 ineligible

Looking at the example above, each payment has a status (paid, eligible, ineligible). When a person's status is changed to inactive, all payments that have not been paid as of the inactive date become ineligible. A percentage of those payments that are now ineligible become due from my client. When the report is generated, I enter a payment begin date of 8/16 and end date of 8/20 (current date), but if a person's status is inactive i need to override the end date in order to report on the payments from 8/23 and 8/30. Thus, when this report is generated again on 8/30 for the next billing cycle, I don't want the payments of 8/23 and 8/30 included in the bill. I hope this is helpful.
Thanks for the previous response!