Minimum Value of three fields

  • Thread starter Thread starter Denise
  • Start date Start date
D

Denise

I am trying to get the minimum value of three fields and
cannot get it to work like excel. In the query, I tried
using FieldName: Min([Field1]+[Field2]+[Field3].

Does anyone know how to do this in Access?

Thank you.
 
Denise said:
I am trying to get the minimum value of three fields and
cannot get it to work like excel. In the query, I tried
using FieldName: Min([Field1]+[Field2]+[Field3].

Does anyone know how to do this in Access?
Click to expand...

Aggregate functions in Access (Sum, Min, etc.) work across rows, not
columns or a set of supplied values.

Try...

IIf([Field1]<[Field2], IIf([Field1]<[Field3], [Field1], [Field3]),
IIf([Field2]<[Field3], [Field2], [Field3]))
 
Denise,

I use a function I created to do this type of thing. Create a code module
and paste the following code into it. You can then use this function in
your query something like:

MinValue: Minimum([Field1], [Field2], [Field3])

The nice thing about this function is that you can use it to compare any
type of field data, so if you want to compare dates, pass it dates, if you
want to compare text, pass it text. The other advantage is that you can
pass it as many values as you want, since it uses a parameter array.

Public Function Minimum(Paramarray varArray()) as variant

Dim intLoop as Integer
Minimum = NULL
For intLoop = LBOUND(varArray) to UBOUND(varArray)
if ISNULL(Minimum) OR varArray(intLoop) < Minimum then
Minimum = varArray(intLoop)
endif
next intLoop

end Function

HTH
Dale
 
Back
Top