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:
- Define a custom VBA function.
- 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];
