Compare multiple values to select lowest value

  • Thread starter Thread starter Burnett71
  • Start date Start date
B

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?
 
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
 
ooops, you wanted the min, not the max....

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



Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula to select lowest value 2
The lowest of all? 6
Finding lowest value 5
Access Compare records in same access table 0
Value match with an array 1
compare values 3
Compare two fields in table and delete oldest record 6
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1

Back
Top