find latest result

[hsandwick]

I have multiple records with the following structure ...
RO1NR04913-01
RO1NR04913-01A1
RO1NR04913-02
RO1NR04913-03

If, e.g., 01A1, then it has an amendment, if it falls into the next year the amendment is dropped and it becomes 02.

I need to find the latest record, so in the example above, it would be the -03 result. If -02 and -03 did not exist, then the latest record would be 01A1.

Does anyone have a substr query that would retrieve the latest results? Thanks in advance!

 

[synapsevampire]

A quick and dirty solution is to group by a formula that contains the first 10 characters, or the characters up through the dash, and then display your fields in the group footer only, as in:

left({table.field},10)

or you might use the following if it's not fixed length:

left({table.field},instr({table.field},"-&quot)

-k

 

[hsandwick]

ok, tks, but how do i retrieve only the latest result based on the characters after the "-"? Is there some way other than, e.g.,
if -01 and -02 and -02A1, then -02A1
or
if -01 and -02, then -02
or
if -01 and -02 and -02A1 and -03, then -03

?

 

[synapsevampire]

Try my solution, it will do exactly what you asked...

Once grouped, the details are sorted, and the last will be the latest based on your description, and that's what will show in the group footer.

You might also use the record selection formula->Group to obtain only the most recent, but the above should suit your purposes since all you want to do is find the latest record.

-k

 

[hsandwick]

Neato! It worked! Thanks for the speedy response. H