How do I use MAX on a field in record selection

[nord]

I'm trying to get the record that has the latest date. Right now I'm getting two records. How do I get the more current record only, based on the date field?

 

[Naith]

Maximum({Field})

 

[nord]

Thanks Naith,

My next hurdle is how to ge tthe SQL statement into the record selection. I tried this with the Select Expert, but I dont see a maximum in the drop down. DO I have to go into the Show SQL Query to modify it manually?

 

[Naith]

If you actually write out:

{Whatever} = Maximum({Whatever}), then that will be passed to the SQL. Even if it isn't, Crystal will sort it for you so that you only see the greatest value.

You don't write the SQL in the record selection. You write the clause, and Crystal generates the SQL at runtime.

Naith

 

[synapsevampire]

I don't think that will work in the record selection criteria, Naith...

Try using a SQL Expression that returns the maximum date, as with SQL Server, but then you have GROUP BY concerns if there are other fields involved:

max(MyDate)

I'd use an SP or View.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com