In a query I could do it with up to 29 arguments, but with 35 arguments any
function that I know of would fail in a query.
And that many fields is too probably too many to use in a normalizing query.
A workaround that could do it.
Paste the following into a module and use it twice in the query in a
calculated field, that would look something like the following.
fgetmaxnumber(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,,FgetmaxNumber(20,21,22,23,24,25,26,27,28,29,30,31,32,33,34))
'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0)
returns 7
'Ignores values that cannot be treated as numbers.
Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double
vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next
If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If
End Function
Dale Fye's Version
Public Function MaxVal(ParamArray MyArray()) As Variant
Dim varMax As Variant
Dim intLoop As Integer
varMax = Null
For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax
End Function
