Nested IIF Statement

Before I go into detail on what a Nested IIF Statement a quick overview of an IIF statement is in order.

What is an IIF Statement

First of all an explanation of a simple IIF statement. An IIF statement consists of the following:

If an Expression is true, then do this, otherwise do that.

i.e.

IIF(Field1 = x, (True)then do this ,(False) if not then do that)

now with some data.

IIF (Field1 = 10, Field2 = 'Good', Field2 = 'Bad')

In the above example, if Fileld1 has the value of 10 then 'Good' will be displayed in Field2, if Field1 <> 10, then 'Bad' will be displayed in Field2.

Got that?? Time to move on.

Nested IIF Statement Example

When you have many IIF statements within an expression it is know as a nested IIF statement.

Why would you use one? Well, They are used if you have more than one true or false option.

As an example, the tip below displays a different option for each colour of a traffic light. As a traffic light has 3 colours, you need the ability to display 3 different options: Stop, Go and Caution.

For this tip I am using a query, but you can just as easily use this in an unbound text box on a form or a report.

For a query, the syntax would go into a blank column in the first row:

First of all give your new field a name. I have called mine 'WhatToDo', and the complete syntax looks like this.

WhatToDo:If([Field1]="Red","Stop",IIf([Field1]="Green","Go","Caution"))

To explain this, if [Field1] contained the value 'red', then when the query is run the value 'stop' would be displayed.

If the value of [Field1] does not equal 'red', then the IIf statement would move to the false part (after the second comma). The false part is another IIF statement, this time it checks if the value of [field1] is 'Green'. If it is, the word 'Go will be displayed when the query is run.

If the value of [Field1] is not ‘Green’ (and not ‘Red’), then the last argument will be displayed, 'Caution'.Which is the final false option.

The above example has two nested statements. I believe you can have up to 7 (possibly) more, but cannot find a definitive answer, but if you were to have more than 2 or 3 nested statements, I would suggest you use either some code to build your own function or use Microsoft Access's Switch function 

Return from Nested IIF Statement to Queries Index