# Thread: Max of Expressions (WinXP/Office 2003)

1. ## Max of Expressions (WinXP/Office 2003)

Is there an easy way, other than hairy nested IIF's to return the max of serveral expressions? For example Max(n1, n2, n3) where n1, n2 & n3 are expressions that give numeric results.

2. ## Re: Max of Expressions (WinXP/Office 2003)

Why not write a public function then use that in a query, eg GetMax(n1, n2, n3)

3. ## Re: Max of Expressions (WinXP/Office 2003)

Thanks for your interest. Can you suggest some code? Preferably, simple!!

4. ## Re: Max of Expressions (WinXP/Office 2003)

This is air code, ie totally untested.

Public Function GetMax(V1 as String, V2 as String, V3 as String) as String
If V1 > V2 then
If V1 > V3 then
GetMax = V1
Else
If V3 > V2 then
GetMax = V3
End If
End If
Else
If V2 > V3 then
GetMax = V2
Else
GetMax = V3
End If
End If

End Function

5. ## Re: Max of Expressions (WinXP/Office 2003)

I learn something everyday, I didnot know about ParamArray as a way of defining an array as arguments to a function. Also UBound to find the number of items in the array.
Nice solution that one.

6. ## Re: Max of Expressions (WinXP/Office 2003)

Activate the Modules tab of the database window.
Click 'New'.
Copy or type the following code:

Function MaxVal(ParamArray Args())
Dim i As Integer
Dim RetVal
RetVal = Args(0)
For i = 1 To UBound(Args)
If Args(i) > RetVal Then
RetVal = Args(i)
End If
Next
MaxVal = RetVal
End Function

And use it like this in a query

MyMax: MaxVal(n1,n2,n3)

or like this in the control source of a text box on a form or report:

=MaxVal(n1,n2,n3)

Of course, you must substitute something meaningful for n1, n2 and n3. The MaxVal function is not the most efficient possible, but it will work with a variable number of arguments, and with different types of arguments (numbers, dates, strings)

7. ## Re: Max of Expressions (WinXP/Office 2003)

Strictly speaking, UBound is not the number of items. If you declare an array as follows:

Dim MyArray(3 To 7) As Integer

LBound(MyArray) = 3 and UBound(MyArray) = 7, while the number of items is 7 - 3 + 1 = 5. For a ParamArray. the LBound is 0 (unless the module contains Option Base 1, in that case LBound = 1), and UBound = number of items - 1.

Note: there was an error in my original code; I corrected this silently.

8. ## Re: Max of Expressions (WinXP/Office 2003)

Thanks to you both for a neat & comprehensive solution!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•