How do I get minimum and maximum values in Microsoft Access

Access provides MAX and Min Functions only in the aggregate view. What if you want either the maximum or minimum values of a series of numbers. Visual basic is conspicuously lacking these features. The two functions below takes an array of values and returns either the max or min values of the array.


'---------------------------------------------------------------------------------------
' Procedure : fArrayMax
' DateTime : 8/13/2002 09:03
' Author : bermanr
' Purpose :Returns the maximum value
'---------------------------------------------------------------------------------------
'
Public Function dhArrayMax(varArray As Variant) As Variant
On Error GoTo dhArrayMax_Error
Dim varitem As Variant
Dim varMax As Variant
Dim i As Long

If IsArray(varArray) Then
If UBound(varArray) = -1 Then
fArrayMax = Null
Else
varMax = varArray(UBound(varArray))
For i = LBound(varArray) To UBound(varArray)
varitem = varArray(i)
If varitem > varMax Then
varMax = varitem
End If
Next i
fArrayMax = varMax
End If
Else
fArrayMax = Null
End If

On Error GoTo 0
Exit Function

dhArrayMax_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fArrayMax of Module modTest"

End Function


'---------------------------------------------------------------------------------------
'Procedure: farraymin
' DateTime : 8/13/2002 08:29
' Author : bermanr
' Purpose : Return the minimum value from an array
'---------------------------------------------------------------------------------------
'
Public Function fArrayMin(varArray As Variant) As Variant
Dim varitem As Variant
Dim varmin As Variant
Dim i As Long
On Error GoTo dhArrayMin_Error
If IsArray(varArray) Then
If UBound(varArray) = -1 Then
fArrayMin = Null
Else
varmin = varArray(LBound(varArray))
For i = LBound(varArray) To UBound(varArray)
varitem = varArray(i)
If varitem < varmin Then
varmin = varitem
End If
Next i
fArrayMin = varmin
End If
Else
fArrayMin = varArray
End If

On Error GoTo 0
Exit Function

dhArrayMin_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure dhArrayMin of Module modTest"

End Function