Selecting the latest date without using maximum

[JamesFlowers]

I have a recordset of 420 records grouped by ordernumber with values based upon different dates (to represent increase in prices)
There are three valid dates for currency with these records

as below

ORDERNO LINENO VALUE DATE
0000001 01 1.00 01/01/2001
0000001 01 2.00 01/02/2001
0000001 01 3.00 01/03/2001

0000002 01 1.50 10/01/2001
0000002 01 1.75 10/02/2001
0000002 01 1.85 10/03/2001

I am running this report at a current date (in this case 15/02/2001.

this is the data I require.

ORDERNO LINENO VALUE DATE
0000001 01 2.00 01/02/2001
0000002 01 1.75 10/02/2001

to elimnate the last line I can do
{CURRENTDATE}<{DATE}

which results in

ORDERNO LINENO VALUE DATE
0000001 01 1.00 01/01/2001
0000001 01 2.00 01/02/2001

0000002 01 1.50 10/01/2001
0000002 01 1.75 10/02/2001


How can i now restrict it to choose the required dates (I cant use MAXIMUM, as this results in the

ORDERNO LINENO VALUE DATE
0000002 01 1.75 10/02/2001

as being the only date returned!

I thank anyone in advance

James Flowers

http://www.trikon.com.

 

[kenhamady]

Create a Summary field that is the Maximum of each group. Then use that in your Group Selection formula. It will look something like this:

{Date} = Maximum({Date},{Order})


Keep in mind that using group selection will require that all totals in the report be running totals. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)