Dear All,

I would like to know if we have a way to know what date is the latest date for each project. For example

Project Date

1 2003-01-01

2 2003-10-01

1 2003-12-01

2 2003-11-01

So For Project 1, latest date is 2003-12-01 and for project 2, latest date is 2003-11-01.

IS there a way to achieve this?

Thanks All

Anh Tran

Are these dates in the same field?

Will sorting by the date do what you want?

Lee

YEs the date is the same field but when sorting you can see it but not shows up automatically for you

Anh Phu

And you won't unless you change your view from Form to Table View, or create a new layout as Columnar (List View).

Lee

You need to sort first by Project number and then by project date. Or, create a selfjoin relationship by Project, and then make a calculated field cLatestDate with the formula:

Max(selfjoin::ProjectDate)

Hi Bob,

Is Max working with date fields ?

It was the last time I checked.

Oh yes. I've made a test.

I don't know why I was almost sure Max was only returning numbers.

Good to know.

BTW, did somebody found a way Max(num with some text) would return num with some text.

INV1520 ----->INV1520

If the text part is always the same then just do:

"INV" & Max(sj::InvNo)

The Max function should return the maximum of the numeric part, and then you just stick the "INV" onto the front again.

Hi Bob,

I had already tried this one (that was also your suggestion) but it doesn't seems to work for me !

You may have to use an intermediate calculated field

cNumericPart = TextToNum(InvNo)

Then use:

"INV" & Max(sj::cNumericPart)

I'll give it a try. Thanks.