Access Maximum Function: Return the second highest value across 10 fields

[liamthequietman]

If there is a column(s) with a -1 it is returning the lowest number, not the second lowest.

Columns without -1 are returning the correct second lowest number

Columns with all -1 returns -1 which is perfect

[twinnyfo]

Have you stepped through the code while it is running to see how the evaluations of the values are working? Particularly, when there is a -1 value and the code gets here:

If FieldArray(I) < currentVal Then
    secondLowest = currentVal
    currentVal = FieldArray(I)
Else
    If FieldArray(I) < secondLowest _
        And FieldArray(I) > currentVal Then
        secondLowest = FieldArray(I)
    End If
End If

How are the evaluations responding?

Not sure if it has anything to do with it, but you may need to declare your array as a Double?

Function Minimum(ParamArray FieldArray() As Double)

[liamthequietman]

Being a complete VBA novice, I'm not familiar on how to step through the code. I placed my cursor in the code and pressed F8 and received a ping which I associate with an error.

Declaring the array as Double resulted in a error

[twinnyfo]

No worries, we've all been novices at one point.

Before you run your code, click your mouse to the left of the first line of your function in the VBA editor. It should leave a dark brown circle in the left margin. Run your code and the code should stop at that point (called a break point). Then, hit F8 repeatedly to watch the code work through itself. Any time the code is paused, you can hover your mouse over any of the variables and you will be able to see their current values.

Hope that hepps!

[liamthequietman]

Thanks for not giving up on this project!

My step through attempt did not work, a Macro box appears (see pic).

I found another issue with the results. In review:

CORRECT

Columns without -1 are returning the correct second lowest number

Columns with all -1 returns -1 which is perfect

INCORRECT

If there is a column(s) with a -1 it is returning the lowest number, not the second lowest.

If there are columns with identical numbers it's returning zero

[twinnyfo]

By "running your code", I mean you have to execute it the way you would normally execute it. Then, when the code hits your break point, then you can step through it using F8. Hope that hepps.

[twinnyfo]

Or, maybe try using the values -2 and -1.2 to see if that will have any effect on the logic the code returns. At this point, I am grasping at straws, because based upon what I am seeing int he code, it "should" work, but it just doesn't. I must be missing something obvious, which would not be the first time something like that happened.

[twinnyfo]

Was able to do some testing and proofing. Note lines 23-31. We forgot about the situation when the first number is the lowest. We never checked to see if the second was still the lowest value. This should solve it.

Public Function Minimum(FieldArray() As Variant) As Single
On Error GoTo EH
    ' Declare the two local variables.
    Dim I               As Integer
    Dim currentVal      As Single
    Dim secondLowest    As Single
    Dim intCount        As Integer

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

    ' Cycle through each value from the row to find the lowest.
    For I = 0 To UBound(FieldArray)
        If FieldArray(I) = currentVal Then intCount = intCount + 1
        If FieldArray(I) < currentVal Then
            secondLowest = currentVal
            currentVal = FieldArray(I)
        ElseIf secondLowest = currentVal _
            And FieldArray(I) > secondLowest Then
                secondLowest = FieldArray(I)
        Else
            If secondLowest = currentVal _
                And FieldArray(I) > secondLowest Then
                secondLowest = FieldArray(I)
            Else
                If FieldArray(I) < secondLowest _
                    And FieldArray(I) > currentVal Then
                    secondLowest = FieldArray(I)
                End If
            End If
        End If
    Next

    If intCount - 1 = UBound(FieldArray) Then
        ' Return the starting value since they were all the same
        Minimum = FieldArray(0) 'Modify this to whatever you want when they are all the same
    Else
        ' Return the Minimum value found.
        Minimum = secondLowest
    End If

    Exit Function
EH:
    MsgBox "There was an error finding the second Lowest value!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

[liamthequietman]

Can I attach a test version of my database?

[twinnyfo]

Check my last post and see if that code works. It ran fine for me. I knew I was overlooking something obvious. It wasn't until I was able to step through the code that I found it.

[liamthequietman]

I applaud you for your tenacity! The expression I enter into the query has worked in all your codes except now. Error wrong number of arguments:

Expr1: Minimum([nPP1SHF],[nPP2SHF],[nPP3SHF],[nPP4SHF],[nPP5SHF],[nPP6SHF],[nPP7SHF],[nPP8SHF],[nPP9SHF],[nPP0SHF])

[twinnyfo]

I changed Line 1 from:

Public Function Minimum(ParamArray FieldArray() As Variant) As Single

to:

Public Function Minimum(FieldArray() As Variant) As Single

Not sure if that makes a difference. I couldn't get it to work any other way.

I was using my code from within VBA, you are calling it as an expression in a Query, which is causing the disconnect.

[liamthequietman]

I found a code which works perfect EXCEPT ...

If there is a column(s) with a -1 it is returning the lowest number, not the second lowest.

Otherwise all the result are correct.

Function SecondMinimum(ParamArray FieldArray() As Variant) As Variant
    Dim I As Integer
    Dim LowestVal As Variant
    Dim secondVal As Variant
    
'   Default values
    LowestVal = Null
    secondVal = Null
    
'   Populate the LowestVal and secondVal temporary variables
    For I = 0 To UBound(FieldArray)
        If IsNull(FieldArray(I)) = False Then
            If IsNull(LowestVal) Then
                LowestVal = FieldArray(I)
            ElseIf IsNull(secondVal) Then
'               Before populating the secondVal, make sure LowestVal will be
'               less than or equal to secondVal.
                If FieldArray(I) > LowestVal Then
                    secondVal = FieldArray(I)
                Else
                    secondVal = LowestVal
                    LowestVal = FieldArray(I)
                End If
                Exit For
            End If
        End If
    Next I
    
'   Make sure there are at least two non-Null values in the temporary variables
    If IsNull(LowestVal) = False And IsNull(secondVal) = False Then
        
'       Loop through the array values, comparing them to the lowest and second-lowest
        For I = 0 To UBound(FieldArray)
'           Skip duplicate values.
            If FieldArray(I) <> LowestVal Then
'               New lowest value?
                If FieldArray(I) < LowestVal Then
'                   New lowest value.  Push the Lowest up to second, then make this
'                   array value the new lowest value.
                    secondVal = LowestVal
                    LowestVal = FieldArray(I)
'               Skip duplicate values.
                ElseIf FieldArray(I) <> secondVal Then
'                   New second-lowest value?
                    If FieldArray(I) < secondVal Then
'                       Replace the second-lowest value with the current array value.
                        secondVal = FieldArray(I)
                    End If
                End If
            End If
        Next I
    End If
    
'   This function will return Null if less than 2 non-Null values were passed.
    SecondMinimum = secondVal
    
    ' Expr1: SecondMinimum([nPP1SHF],[nPP2SHF],[nPP3SHF],[nPP4SHF],[nPP5SHF],[nPP6SHF],[nPP7SHF],[nPP8SHF],[nPP9SHF],[nPP0SHF])
    
End Function

[twinnyfo]

Did you adjust your code to match what I provided? It worked perfectly for me with all values.

[liamthequietman]

I just ran the code in post #23. When I place the expression into the query I receive, error wrong number of arguments:

Expr1: Minimum([nPP1SHF],[nPP2SHF],[nPP3SHF],[nPP4SHF],[nPP5SHF],[nPP6SHF],[nPP7SHF],[nPP8SHF],[nPP9SHF],[nPP0SHF])