Selection based on 1 or more date ranges being true

[archerks]

I want a report the shows Part Numbers that have had activity in any one of three date fields that during a date range.
I have the following.
PartNum which is the partnumber..
Date1 which is a partnumber revison date..
Date2 which is the last date the partnumber sold..
Date3 which is the last date this item was added to inventory..
If any of these date fields fall between the currentdate-547 and currentdate-730 (that would be between a 1 1/2 and 2 years ago) I need the Partnumber to show on the report. I am at a loss on how to put all of that into the selection formula.

Dave

 

[APB1981]

Hi Dave,

You can create a formula called: 'date check'

In this formula you could create a formula like the following:

//Date1 which is a partnumber revison date..

If {Partnumber.revdate} in (currentdate-547) to (currentdate-730) then 'PASS' else

//Date2 which is the last date the partnumber sold..

If {Partnumber.solddate} in (currentdate-547) to (currentdate-730) then 'PASS' else

//Date3 which is the last date this item was added to inventory..

If {Partnumber.lastdate} in (currentdate-547) to (currentdate-730) then 'PASS' else

'FAIL'


Add this formula to the report and see if it is working. If it is, then...in the record selection add the following:

{@date check} = 'PASS'

I am not sure if this would work though...

APB

 

[APB1981]

Actually the dates should be:

....in (currentdate-730) to (currentdate-547)

APB

 

[Madawc]

APB1981's formula would work but could be simplified. AE.g. formula field called @GoodDate,

{Partnumber.revdate} in (currentdate-547) to (currentdate-730) 
or
{Partnumber.solddate} in (currentdate-547) to (currentdate-730) 
or
{Partnumber.lastdate} in  (currentdate-730) to (currentdate-547)

This would display on the report as True or False, but in Record Selection it would be enough to say @GoodDate.

This is a boolian and they are one of Crystal's useful features. If the formula gave trouble, you could always split it into three, @GoodDate1, @GoodDate2 and @GoodDate3, display without selection and see which wasn't giving the result you were expecting.

Depending on your database, it might also be wise to test for null values in a data. Any Crystal formula will stop if it hits a null, even if there is an ISNULL test later on in the formula. One of the things that confused me when I first started using Crystal.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10