You can use a custom vba function to do this. The need to do this and
use VBA to do so implies that your table structure is not correct. You
have repeating fields containing the same type of data. That usually
means that you should have another table to contain the repeating data.
'------------- Code Starts --------------
Public Function fRowMax(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.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to nest fRowMax calls for groups of fields.
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
fRowMax = vMax
Else
fRowMax = 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
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
