Lowest value of 4 fields in record

  • Thread starter Thread starter RoBo
  • Start date Start date
R

RoBo

Who can give me a hand?

I would like to determine what the lowest value is of four fields in a
query. One or more fields can have a NULL value and if all fields are NULL,
the value zero should be returned. Is this possible and if, how?

Thanks for any help to this question.

Ron
 
Simplest way would be to use a custom VBA function.

Here is one from long ago.

Public Function fRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the minimum Number of a group of values passed to it.
'Sample call: myMin = GetMinNumber("-21","TEST","2","3",4,5,6,"7",0)
' returns -21
'Ignores values that cannot be treated as numbers.
'
'Max of 29 arguments can be passed to a function in Access SQL


Dim i As Integer, vMin As Variant
Dim tfFound As Boolean, dblCompare As Double

vMin = 1E+308 'very large positive number
For i = LBound(Values) To UBound(Values)

If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare < vMin Then
vMin = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fRowMin = vMin
Else
fRowMin = Null
End If

End Function


End Function
 
Back
Top