Max - Simple?

V

Vinny

In Access Visual Basic I am trying to find the max value from a series
of attributes. So, for example, we have field1 with value 5, field2 with
value 3 and field3 with value 1. To me it would sound logical if it
would be something like Max(field1, field2, field3). However the Max
function does not exist in VisualBasic?
 
B

Brendan Reynolds \(MVP\)

Jet SQL has a Max() function, but it returns the maximum value of a single
field in a table or query. To get the maximum value across fields, you'll
need to either use a bunch of nested IIf() functions (ugly and difficult to
maintain if there's more than two or three fields) or write your own code.
Here's an example, I haven't extensively tested it, but hopefully it should
at least serve as a starting point ...

Public Function fMax(ParamArray values() As Variant) As Variant

Dim varWork As Variant
Dim lngLoop As Long

varWork = values(LBound(values))
For lngLoop = LBound(values) To UBound(values)
If values(lngLoop) > varWork Then
varWork = values(lngLoop)
End If
Next lngLoop

fMax = varWork

End Function

Examples of use, in the Immediate window ...

? fmax(1,3,5,-1)
5
? fmax("b","d","c","1")
d
 
W

Wayne Morgan

Check the DMax function in the VBA help file. The VBA help file can be gotten to by
opening help while in a code window.
 
Top