Compare multiple values to select lowest value

[Burnett71]

For some reason, I can't figure this out...

I have 3 currency fields in a table with varying amounts. I have a query in
which I need to compare the amounts in these three fields, then select the
lowest value for use in a subsequent calculated field. I'm hoping and
looking for a simple function or module to accomplish this. Any suggestions?

 

[vanderghast]

MaxPrice: SWITCH( price1>=price2 AND price1>=price3, price1,
price2>=price3, price2, true, price3)


as computed expression, then, use MaxPrice as required (except in the WHERE
clause, or in the ORDER BY clause). If you need to use that value in the
orderby clause, or in the where clause, save that query and use another
query that will use that saved query.

I assumed your prices are NOT null.


Vanderghast, Access MVP

 

[vanderghast]

ooops, you wanted the min, not the max....

MinPrice: SWITCH( price1<=price2 AND price1<=price3, price1,
price2<=price3, price2, true, price3)



Vanderghast, Access MVP

 

[JimBurke via AccessMonster.com]

I don't know of a Min function in VBA - if there is one you would just use
that, e.g. create a new field using something like:

MinValue: Min(Cur1,Cur2,Cur3)

Assuming there is no Min function, the only way I can think of to do this in
that same query is to create a new field based on an IIf statement. I'll call
the field with the minimum value of the three MinValue, and I'll call the
three currency fields Cur1, Cur2, Cur3. Create a new field in the query like
this:

MinValue: IIf(Cur1 < Cur2 and Cur1 < Cur3, Cur1, IIf(Cur2 < Cur1 and Cur2 <
Cur3, Cur2,Cur3))

Pretty sure that should work, assuming all 3 currency fields will always have
values. If it's possbile some or all 3 currency fields may have null values
sometimes, then you'd need to decide what you want to do in case of a null
value. If you wanted to treat a null value as 0 you'd have to replace the
field name in the comparison using the nz function, e.g. nz(Cur1,0) < nz(Cur2,
0), etc. If you don't want to use a null value as a minimum you could use Nz
and assign a very high number that you know will always be < the other
numbers, e.g. Nz(Cur1,999999) or something like that.

 

[Burnett71]

Thank you for the help. It worked beautifullly.

 

[Burnett71]

Thank you for your response. Definitely gave me what I needed to finish this
job out.