Finding Min, Max, and next before max

[The Big Bear]

Hi all

I can find the min and max date a welder has welded, but i also need to find the date before the max date.

example:

Terry welds on 1/3/05

3/6/05

4/6/05

5/6/05

7/24/05

8/23/05

I can find the first date using "min" and the last date using "max" but i can not figure out how to find the date before "max" which would be "7/24/05".

Any ideas and suggest would be greatly appreciated

Thnaks in advance

Lionel

[Søren Dyhr]

Show All Records 

Set Error Capture [ On ] 

Enter Find Mode [  ] 

Show Custom Dialog [ Title: "Oi Dude"; Message: "Enter name of the welder"; Buttons: "OK"; Input #1: Untitled::WelderName ] Perform Find [  ] 

Sort Records [ Specified Sort Order: Untitled::Days; ascending ] [ Restore; No dialog ] 

Set Error Capture [ Off ] 

If [ Get ( FoundCount ) > 2 ] 

     Go to Record/Request/Page [ First ] 

     Omit Multiple Records [ Get ( FoundCount ) - 2 ] [ No dialog ] 

     Go to Record/Request/Page [ Next ] 

     Omit Record 

Else 

     Show Custom Dialog [ Title: "Error!"; Message: "Nothing found"; Buttons: "OK" ] 

End If 

Dang ...you're on 6.0 then do you have to...

Show All Records 

Set Error Capture [ On ] 

Enter Find Mode [  ] 

Show Custom Dialog [ Title: "Oi Dude"; Message: "Enter name of the welder"; Buttons: "OK"; Input #1: Untitled::WelderName ]

Perform Find [  ] 

Sort Records [ Specified Sort Order: Untitled::Days; ascending ] [ Restore; No dialog ] 

Set Error Capture [ Off ] 

If [ Get ( FoundCount ) > 2 ] 

    Go to Record/Request/Page [ Last ] 

    Omit Record 

    Loop 

            Go to Record/Request/Page [ Previous; Exit after last ]

            Omit Record 

    End Loop 

Else 

Show Custom Dialog [ Title: "Error!"; Message: "Nothing found"; Buttons: "OK" ] 

End If 

--sd

Edited August 25, 2005 by Guest

[comment]

Define a value list "Dates", using values from the date field, include only related values, starting from your welders table.

Then, in the welders table define a calculation field (unstored, result is date) =

Let ( [

list = ValueListItems ( Get (FileName) ; "Dates" ) ;

last = ValueCount ( list )

] ;

GetAsDate ( MiddleValues ( list ; last - 1 ; 1 ) )

)

[comment]

Oh, version 6, bummer - I don't even know which functions are available in 6.

Try:

Middle(

ValueListItems(Status(CurrentFileName). "Dates") ,

Position(

ValueListItems(Status(CurrentFileName), "Dates") , "¶" , 1 , PatternCount( ValueListItems(Status(CurrentFileName), "Dates") , "¶") - 1 ) + 1 ,

Position(

ValueListItems(Status(CurrentFileName), "Dates") , "¶", 1, PatternCount( ValueListItems(Status(CurrentFileName), "Dates") , "¶") )

- Position(

ValueListItems(Status(CurrentFileName), "Dates") , "¶" , 1 , PatternCount( ValueListItems(Status(CurrentFileName), "Dates") , "¶") - 1 ) - 1

)

[Søren Dyhr]

Dear me Comment....Hows about

RightWords(

Substitute(ValueListItems(Status(CurrentFileName), "Dates"); 

RightWords(ValueListItems(Status(CurrentFileName), "Dates");

 3); ""); 3)

If we're to be in almost excell mode

--sd

Edited August 25, 2005 by Guest

[The Big Bear]

SOren Dyhr and comment

thanks a bunch both of you are life savers

Thanks

Lionel

[comment]

I have stayed away from counting words, because the result depends on the date format. In pre-7 versions:

8/25/2005 = 3 words;

25-8-2005 = 3 words;

but

25.8.2005 = 1 word.

---

I am sorely tempted to accuse you of being an Americano-centrist and ignoring the rest of the world!!!

Edited August 25, 2005 by Guest

[Søren Dyhr]

I am sorely tempted to accuse you of being an Americano-centrist and ignoring the rest of the world!!!

Good grief, couldn't I pursuade you to call me "Daft as a brush" instead ...the other is unbearable : : - But the script then, it had some bite didn't it???

--sd