Lowest value of 4 fields in record

[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

 

[John Spencer]

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