Find the latest Date In many records

[Anh_Tran]

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

[Lee Smith]

Are these dates in the same field?

Will sorting by the date do what you want?

Lee

[Anh_Tran]

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

Anh Phu

[Lee Smith]

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

Lee

[BobWeaver]

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)

[Ugo DI LUCA]

Hi Bob,

Is Max working with date fields ?

[BobWeaver]

It was the last time I checked.

[Ugo DI LUCA]

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

[BobWeaver]

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.

[Ugo DI LUCA]

Hi Bob,

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

[BobWeaver]

You may have to use an intermediate calculated field

cNumericPart = TextToNum(InvNo)

Then use:

"INV" & Max(sj::cNumericPart)

[Ugo DI LUCA]

I'll give it a try. Thanks.