Microsoft Access 2003 finding the minimum and maximum column values using a VBA function

If you may have to write a query in MS-Access 2003 to get the maximum or minimum of the values of two columns, you could use the iif() expression like this:iif(n1 > n2, n1, n2) or iif(n1 < n2, n1, n2) to obtain the maximum or minimum, respectively. The expression becomes more complicated when you have to compare three columns:

IIf(n1>n2,
  IIf(n1>n3,n1,n3),
  IIf(n2>n3,n2,n3))

Such expressions are hard to get right, and let's not even consider writing one to compare four columns! (In case you were wondering, we can't use Access' max() and min() aggregate functions because they operate on rows.)

Access allows you to call VBA functions in queries, so we can replace the long and cumbersome iif() expression with a call to a custom VBA function. In the rest of this article, we will:

1. Define a custom VBA function.
2. Calling a customer VBA function.

Define a custom VBA function

Here's a simple VBA function one to find the maximum of a list of values (just change the name and reverse the comparison to find the minimum):

Function max_value(ParamArray n() As Variant)
  Dim i As Integer
  Dim ret As Variant
  ret = n(0)
  For i = 1 To (UBound(n))
    If ret < n(i) Then ret = n(i)
  Next i
  max_value = ret
End Function

The ParamArray declares n() as a variable argument list (or varargs in C). Using ParamArray means that the function is not restricted to a pre-defined number of parameters, and using a Variant means that the function will work for different types of data, such as numbers and dates.

The body of the function just loops through all the values in the argument list and returns the largest value.

Calling a custom VBA function

If you have a table called Number with fields n1, n2, n3, n4, n5, you can call this function in a query, just like any other built-in function, like this:

SELECT max_value(n1, n2, n3, n4, n5) as max_value, n1, n2, n3, n4, n5 FROM [Number];