Betty,
MIN and MAX will give you the minimum or maximum value in a field in
your table. To do what you want you need a function, which I have
already written and am including below. The two functions fnMin() and
fnMax() will allow you to compute the minimum or maximum values among
a group of values that are passed to the function (these can be alpha,
numeric, date, or any combination thereof).
Copy these functions and past them in a code module, then call them
using the same technique you were trying before:
Minimum: fnMin([field1], [field2], [field3], 0)
or whatever you want.
******************
Public Function fnMin(ParamArray varArgs()) As Variant
'set default return value of the function
fnMin = Null
'If no values are passed, do nothing
If IsMissing(varArgs) Then Exit Function
fnMin = varArgs(LBound(varArgs))
'Loop thru remaining array items looking for smallest value
For intLoop = LBound(varArgs) + 1 To UBound(varArgs)
If fnMin > varArgs(intLoop) Then fnMin = varArgs(intLoop)
Next
End Function
Public Function fnMax(ParamArray varArgs()) As Variant
'set default return value of the function
fnMax = Null
'If no values are passed, do nothing
If IsMissing(varArgs) Then Exit Function
fnMax = varArgs(LBound(varArgs))
'Loop thru remaining array items looking for largest value
For intLoop = LBound(varArgs) + 1 To UBound(varArgs)
If fnMax < varArgs(intLoop) Then fnMax = varArgs(intLoop)
Next
End Function
--
HTH
Dale Fye
I'm trying to calculate a max in a query, but access is
teling me I have too few arguments. My logic is:
Max([field1],0)
Access isn't recognizing any other similar functions (eg
MAXA, MIN, etc). Is there an add-in I am supposed to
have? Thanks!
