Here is a function I wrote quite a bit ago. You can copy it into a module
and save the module.
To use it in your query, you would call it something like the following.
Field: Smallest: fGetMinNumber([Field1]*3,[Field2] /5,[Field1]*[field8] / 3,
Field10 Mod 30)
P.S. do not name the module the same as the function.
'============ Code Starts ==================
Public Function fGetMinNumber(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.
Dim i As Integer, vMin As Variant, 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
fGetMinNumber = vMin
Else
fGetMinNumber = Null
End If
End Function
'=========== Code Ends =================
suechndlr said:
I want to create a calculated field in a query that gives the smallest of 4
possible calculations. I would make a calculated field for each of the
calculations, then the fifth field would be the minimum. It seems the min
function does something else....
