IF Statements In A Query

Question:

In a query with 2 columns based on 2 fields in a table, how can I create a 3rd column that lists the minimum of the other 2 columns?

Answer:

Here is a simple list of sample data for this query.

 Field1      Field2      Smallest
----------------------------------
   5            7            5
   9            3            3
   8            7            7
   1            2            1

Field1 and Field2 come from the table. Smallest is determined by the query.

You might think that there would be an “IF” command that we use in the query. You are almost correct. The command is an “IIF”, or in full, Immediate IF. It is formally called a function and you will find it as part of the VBA programming language.

In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition: the value that you want to test

value_if_true: the value that is returned if “condition” evaluates to TRUE

value_if_false: the value that is returned if “condition” evaluates to FALSE

Here is the query that we need:

Select Field1, Field2, iif(Field1 < Field2, Field1, Field2) as Smallest
From Table1

Enjoy.