Max across fields in a record

[Razor]

Hi,

How would we check across several fields in a record to
extract the highest number (or latest date)?

I know Max() checks across several records in a field but
is there a corresponding function for my requirement?

Thanks,

Razor

 

[John Spencer (MVP)]

You have to create a user function to do this.

'------------- 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.
'But will work with dates since they are really 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