Monday, January 7, 2008

Maximum Value Function

In Excel I can write a formula like =Max(1,2,3). It correctly gives me 3 as the result.

In Access I can use Max in a totals query to find the highest value of a field across all the rows in a table.

But what if I want the highest value across three fields in a single row, or of three variabls in VBA. I can't use Max in that scenario in Access. Here's a user defined function that will work in both those scenarios:

Public Function GreatestValue(ParamArray varValues() As Variant) As Variant

    Dim varMax As Variant
    Dim lngCounter As Long
    varMax = Null
    For lngCounter = LBound(varValues) To UBound(varValues)
        If IsNull(varMax) Then
            varMax = varValues(lngCounter)
            If varValues(lngCounter) > varMax Then
                varMax = varValues(lngCounter)
            End If
        End If
    Next lngCounter
    GreatestValue = varMax
End Function
Paste the code above into a new standard module (not a form module) and you can then do in a query:
SELECT [A],[B],[C],GreatestValue([A],[B],[C]) AS HighestField FROM MyTable
or in VBA:

It should work with any number of values and with numbers or strings.


doccaml said...

Thanks. I had seen the code on the microsoft site, but that only works if the first field doesn't have null values. Your code avoids that problem.

Anonymous said...

Great work! Is there a way to also find the next highest value?

Anonymous said...

Thank you very much1 this help me a lot to calculate the maximum number of days.

Shawn said...

You rock!!! This thing works with dates - I used it for an Access 2007 form, and it calc'd the dates using four field names! Thanks!