MS Access: Compare 3 fields, use the highest value.

leewallin used Ask the Experts™
MS Access 2003 - In a query - I have 3 fields with numerical values.  I want to select the highest value of the 3.  I see a MAX function but can't find good help on it. What's the best way to do this?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

U could use a IF statements within your query


SELECT   IIF(field1>field2, IIF(field1>field3,field1,field3),  IIF(field2>field3,field2,field3)  ) AS MaxValue
FROM mytable
The max function won't help you with this.  The max function works on a single field.

You will have to use iif.

MyMax:iif(field1>=field2 and field1>=field3, field1, iif(field2>field3, field2),field3)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial