Provided by Allen Browne, December 2004. Last updated: April 2010
Access does not have functions like Min() and Max() in Excel, for selecting the least/greatest value from a list. That makes sense in a relational database, because you store these values in a related table. So Access provides DMin() and DMax() for retrieving the smallest/largest value from the column in the related table.
Occasionally, you still need to pick the minimum or maximum value from a list. The functions below do that. They work with numeric fields, including currency and dates. They return Null if there was no numeric value in the list.
To create them:
Use them like any built-in function.
For example, you could put this in a text box:
=MinOfList(5, -3, Null, 0, 2)
Or you could type this into a fresh column of the Field row in a query that has three date fields:
MaxOfList([OrderDate], [InvoiceDate], [DueDate])
Function MinOfList(ParamArray varValues()) As Variant Dim i As Integer 'Loop controller. Dim varMin As Variant 'Smallest value found so far. varMin = Null 'Initialize to null For i = LBound(varValues) To UBound(varValues) If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then If varMin <= varValues(i) Then 'do nothing Else varMin = varValues(i) End If End If Next MinOfList = varMin End Function Function MaxOfList(ParamArray varValues()) As Variant Dim i As Integer 'Loop controller. Dim varMax As Variant 'Largest value found so far. varMax = Null 'Initialize to null For i = LBound(varValues) To UBound(varValues) If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then If varMax >= varValues(i) Then 'do nothing Else varMax = varValues(i) End If End If Next MaxOfList = varMax End Function
The ParamArray keyword lets you pass in any number of values. The function receives them as an array. You can then examine each value in the array to find the highest or lowest. The LBound() and UBound() functions indicate how many values were passed in, and the loop visits each member in the array.
Any nulls in the list are ignored: they do not pass the IsNumeric() test.
The return value (varMin or VarMax) is initialized to Null, so the function returns Null if no values are found. It also means that if no values have been found yet, the line:
If varMin <= varValues(i) Then
evaluates to Null, and so the Else block executes. Since an If statement has three possible outcomes - True, False, and Null - a "do nothing" for one is a convenient way to handle the other two. If that is new, see Common errors with Null.
Note that the functions would yield wrong results if the return value was not initialized to Null. VBA initializes it to Empty. In numeric comparisons, Empty is treated as zero. Since the function then has a zero already, it would then fail to identify the lowest number in the list.
Home | Index of tips | Top |