Record selection for latest dates (two fields)

Hi there.

I was wondering if anyone could help me here.

I need to select record details for the latest dates from two fields. The fields are:

Date1; and
Date2.

I tried to use the Maximum formula, but can not get it to work (i.e it wont select the latest date from each of the fields, and supress all other records):

{RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}= Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}) and
{RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612} = Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

Any help would be great.

Cheers.

Mat.Not quite sure what you are asking, but if you want to select records for only the two most recent dates in the database, use a subroutine to select those dates, then "share" them with the main report.|||Try creating two formulas
@.maxrcf03 would be

Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat})

@.MaxQCF would be

Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

then use the formulas to return your maximum date for each of these fields.|||I tried that, but could not get it working.

Here is a sample of my dataset, where I need to return only information in row 1 (record 1):

Row 1. SPRING Cecil fred 31/05/2007 6/06/2007
Row 2. SPRING Cecil fred 1/06/2007 6/06/2007
Row 3. SPRING Cecil fred 18/06/2007 6/06/2007
Row 4. SPRING Cecil fred 15/06/2007 6/06/2007
Row 5. SPRING Cecil fred 19/06/2007 6/06/2007
Row 6. SPRING Cecil fred 25/06/2007 6/06/2007

The formulae overwrites the whole data range for all clients eg:

SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 1/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007

I'm not sure how to make the formulae record-specific i.e. so it only applies to Mr Spring, anmd not to Mr Winter.

Cheers and thanks for your help.

Mat.|||Group on the field that is SPRING, WINTER etc. I'll call it {table.id}

Add a GROUP selection formula of
{table.date1} = Maximum({table.date1}, {table.id})
and {table.date2} = Maximum({table.date2}, {table.id})

Note that this means that if a particular group does not have a record where *both* of the dates are the latest respective dates within the group then it will not show at all.

An alternative would be to group by {table.id}, sort descending on both date1 and date2 and then print in the group header (or sort ascending and print in the group footer).|||Thanks Jagan.

I'll give that a go tomorrow at work.

cheers.

Mat.