Most recent record, prior to a specified date?

[vibri2001]

I am using Crystal v9

I have a date parameter and I am trying to only select records whose most recent date prior to the date parameter has a particular field = "YES".

I have it working except for only looking at the most recent record prior to the date range parameter.

Any help would be greatly appreciated. Please let me know if you need further information to help with this, I tried to simplify so the rest of record selection didn't confuse this matter.

 

[dgillz]

I would try to conditionally suppress the records, rather than filtering them out in a record selection formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[synapsevampire]

You should post what you currently have in the record selection formula-Record so as not to waste time with guessing what you have so far.

It should be something like:

(
{table.field} < {?MyDateParm}
)
and
(
{table.field} = "Yes"
)

-k

 

[vibri2001]

Here is what I am using currently:

({table1.IS_TOBACCO_USER} = 'Yes' and {table1.CONTACT_DATE} <= {table2.DISCH_DATE_TIME})

Im getting the fairly accurate results. However, 2 of my 12 results have a 'Yes' for that field but they are not the most current row prior to the disch_date_time.

 

[lbass]

Use the following for your record selection formula:

{table1.CONTACT_DATE} <= {table2.DISCH_DATE_TIME}

Then go to report->selection formula->GROUP and enter:

{table1.CONTACT_DATE} = maximum({table1.CONTACT_DATE},{table.groupfield}) and
{table1.IS_TOBACCO_USER} = 'Yes'

I'm assuming you want this selection per some group. Substitute your group field for {table.groupfield}.

-LB