Max - Simple?

  • Thread starter Thread starter Vinny
  • Start date Start date
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?
 
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
 
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.
 
Back
Top