Nested 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.

As mentioned earlier a nested IIF statement is many IIF statements nested within each other.

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'.

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 IIF statements, I would suggest you use either some code to build your own function or use Microsoft Access's Switch function