Multiple Selection Criteria

[wilekoyote76]

What I am trying to do is first select all records that are open. A record would be open if it has an invoice date and no payment.

Invoice records are found in these fields: {CONTACT2.UDATEINV}, {CONTACT2.URIDATINV}, {CONTACT2.URODATEINV}, and {CONTACT2.UINTINVDAT}

Payment info is found in these fields:
{CONTACT2.UPYMTRECD}, {CONTACT2.URIAMTPD}, {CONTACT2.UINTINVPMT}, and {CONTACT2.UROAMTPD}

The invoice fields are noted correspond in number with the payment fields above. For example..if udateinv has a date upymtrecd would have a number greater than 0 if it was paid. If uridateinv has a date, uriamtpd would have a number greater than 0 if it was paid.

I'm trying to get the selection set first and then I have to determine if it is 30/60/90 days past due from the invoice date that has the non value for the payment.

I've been struggling with the selection statement for all the records. The progression for the fields being filled with invoice and payments is as follows:

if UDATEINV has a date, UPYMTRECD must have a number or it is open. If UPYMTRECD is greater than 0 or not null, then potentially URIDATEINV OR URODATEINV could have a date. If URIDATEINV or URODATEINV have no payment in the fields URIAMTPD or UROAMTPD, they are open.

If URIAMTPD and UROAMTPD have payments, then UINTINVDAT must be checked if it has a date. If it does, then it is open unless UINTINVPMT has a payment in it.

All records are being selected using a date parameter checking between the {?BeginDate} and {?EndDate}

Could someone help me get on the right track here? If you are confused, please specify and I will respond.

 

[Madawc]

I'd suggest you write some formula fields that are boolians, something like

not isnull({CONTACT2.URIDATINV})

Display these alone with the raw data, they will say True or False, or be blank if they hit a null and you did not test for it as your first check in the formula field.

Once you have the tests correct, you can combine them and also put them into the selection statement. Just saying @HasDate in record selection would apply the rule.

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