How do I get the minimum of 4 different calculations in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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....
 
Min is used to calculate across records, not across fields. Any time I see a
question about using summary functions across fields, I think "un-normalized
tables".
 
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 =================
 
Thank you!

John Spencer said:
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....
Click to expand...
Click to expand...
 
Back
Top