Getting nth highest value in VBA Access array 1

[kaprmg]

Hello All,

I am really confused on how to get the second highest value in a Access VBA array. I can get the highest value with the below code from the Microsoft website, but I can't find how to get the second and third highest values in different functions.


Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the maximum value found.
Maximum = currentVal

End Function

Thank you in advance.
kaprmg

 

[MakeItSo]

Hi there!

how about using a temp array and TWO sorts?

Dim tmpArray
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next 

tmpArray=Filter(FieldArray, currentVal, False, vbTextCompare)
'Rem: this will fill the tmpArray with all your array values EXCEPT the highest one.

secondHighest= tmpArray(0)
For I = 0 To UBound(tmpArray)
If tmpArray(I) > secondHighest Then
secondHighest= tmpArray(I)
End If
Next

Would that do the trick?


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[PHV]

Function Max2nd(ParamArray FieldArray() As Variant)
' Declare the local variables.
Dim I As Integer
Dim currentVal As Variant
Dim maxVal As Variant
maxVal = Maximum(FieldArray)
' Set the variable currentVal equal to the array of values.
For I = 0 To UBound(FieldArray)
If FieldArray(I) <> maxVal Then
currentVal = FieldArray(I)
Exit For
End If
Next
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal And FieldArray(I) < maxVal Then
currentVal = FieldArray(I)
End If
Next
' Return the value found.
Max2nd = currentVal
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kaprmg]

Thanks MakeItSo. The temp array worked best. I really appreciate the help. It is really nice to have other professionals to turn to.