select latest record based on date AND time

[kskip]

Using Crystal Reports 8.5, and ODBC connection to MS SQL tables.

I'm linking two files(an order header file and a line item file linked on order_no using an equals join).

My report is grouped on Order_no.

The table has fields hst_dt and hst_tm.

I need to select the line item records that have the latest hst_dt (date) and/or (hst_tm).

I was successful with records with *different* hst_dt's using

{table.hst_dt}=maximum({table.hst_dt},{table.ord_no})
in the Report->Edit Selection Formula->Group.

My problem comes about when I have records that have the same hst_dt. This means I need to select based on the hst_tm field.

I have tried

if next({table.hst_dt}) <> {table.hst_dt} then
{table.hst_dt}=maximum({table.hst_dt},{table.ord_no})
else
{table.hst_tm}=maximum({table.hst_tm},{table.ord_no})

but I get an error that this function (next I assume) cannot be used because the it must be evaluated later.


Any one have a suggestion?

 

[lbass]

You should create a datetime field, by using a formula like {@datetime}:

datetime({table.hst_dt},{table.hst_tm})

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

{@datetime} = maximum({@datetime},{table.ord_no})

-LB

 

[kskip]

Thank You!

That worked great.

kSkip