How do I select the record with the latest date?

[MAKU40]

Below is the selection criteria:

({TRACE_INV_TRANS.QTY} > 0.00) and
({INVENTORY_TRANS.WAREHOUSE_ID} = {?Warehouse} or {?Warehouse} = "ALL") and
{INVENTORY_TRANS.LOCATION_ID} = {?Location} and
(Date({TRACE_INV_TRANS.CREATE_DATE}) <= {?EndDate})

The last line is needed but that gives me ALL the dates prior to the selected date. My question is, how do I filter it just to the record with the most recent date that is less then the selected date? Thanks in advance.

MAKU40

 

[Madawc]

If I've understood you correclty, you are wanting to have a record selection criterion based on a comparison between several different records. I don't think Crystal allows that.

You can get the same effect by selecting all suitable records and then suppressing some. Or grouping for a location (if that's what you want) and showing just the group header with the maximum date for that location. (This can be a summary value, and so will be available in the header, unlike a running total.)

It helps to give your Crystal version, since newer versions have extra solutions, and some extra problems. I use Crystal 8.5

Madawc Williams (East Anglia)

 

[synapsevampire]

Try posting technical information:

Crystal version
Database
Example data
Expected output

Also your post asks for 2 different things:

"My question is, how do I filter it just to the record with the most recent date that is less then the selected date? " Yet the example code says less than or equal to, which is it?

Group by whatever field you want the latest date for, and then in the Report->Edit Selection Formula->Group, place something like:

{table.date} = maximum({table.date},{table.groupfield})

This should get you close, if not, please post more tech info.

-k

 

[MAKU40]

Thank you both for the info.

Crystal ver - 8.5
DB - Oracle 8i

I will try your suggestions. Thanks again.