IIF vs SWITCH Function in MS-Access

Introduction.

The IIF() Function.

The IIF() Function definition is reproduced here from Microsoft Visual Basic for Applications Reference below:

The IIf Function Returns one of two parts, depending on the evaluation of an expression.

Syntax: IIf(logical expression, true part, false part)

The IIf function syntax has these named arguments:

Part	Description
expression	Required. The expression you want to evaluate.
true part	Required. Value or expression returned if the expression evaluates to True.
false part	Required. Value or expression returned if the expression is False.

Remarks

The IIf expression always evaluates both the True part and the False part, even though it will return only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating to false in a division by zero error case, an error occurs even if the expression is True.

Example

This example uses the IIf() function to evaluate the TestMe parameter of the CheckIt() Function and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small".

Function CheckIt(TestMe As Integer)
      CheckIt = IIf(TestMe > 1000, "Large", "Small") 
End Function 

Courtesy: Microsoft Access Help

Let us expand the above function to check a series of expressions to return one of the values among them.

Function CheckIt(TestMe As Integer)
      CheckIt = IIF(TestMe > 0 AND TestMe < 256, "Byte",IIF(TestMe > 255 AND TestMe < 32768,"Integer","Large")) 
  End Function 

As you can see from the above example, for testing each expression, we have to nest the IIF() function one within another, when two or more expressions are evaluated, and it is absolutely necessary that the parentheses are paired properly.  This is where we face problems while using this function in Query columns or in criteria rows etc.

If we need a valid value to be returned when none of the expressions evaluated to True (in the above example the text "Large") then IIF() is the correct solution, otherwise, we have a better function Switch().

The Switch Function.

The Switch() Function is very simple to use without nesting of expressions like IIF().  Let us rewrite the CheckIt() Function, with the Switch() function to see how simple it is.

Function CheckIt(TestMe As Integer) 
    CheckIt = Switch(TestMe > 0 AND TestMe < 256, "Byte",TestMe > 255 AND TestMe < 32768,"Integer") 

End Function 

The above function does the same work, but if none of the expressions evaluate to True then the result returned in the CheckIt variable is Null instead of the text "Large".  If you want to check the returned result for Null and replace Null with the text "Large" then rewrite the expression as below:

CheckIt = NZ(Switch(TestMe > 0 AND TestMe < 256, "Byte",TestMe > 255 AND TestMe < 32768,"Integer"),"Large")

OR

x = Switch(TestMe > 0 AND TestMe < 256, "Byte",TestMe > 255 AND TestMe < 32768,"Integer")

CheckIt = Nz(x,"Large")

If you are using the Switch() Function in a Query column or criteria row, then the first example will be used with Switch() enveloped in Nz(). 

I think it is easier and more compact to use the Switch() Function when compared with IIF() which needs repeating the function name IIF with balancing of several parentheses when several expressions are evaluated.

Usage in a sample Query:

UPDATE Employees SET Employees.Designation = Switch(Employees.Designation IS NULL,'UNKNOWN')
WHERE ((Employees.FirstName IS NOT NULL));

See the Switch() Function definition given below for details, taken from Microsoft Access Help Document.

Switch Function Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax;

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part	Description
expr	Required. Variant expression you want to evaluate.
value	Required. Value or expression to be returned if the corresponding expression is True.

Remarks:

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, the Switch returns value-1. If expr-1 is False, but expr-2 is True, the Switch returns value-2, and so on.

Switch returns a Null value if: None of the expressions are True. The first True expression has a corresponding value that is Null. Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

Example:

This example uses the Switch function to return the name of a language that matches the name of a city.

Function MatchUp (CityName As String)

    Matchup = Switch(CityName = "London", "English", CityName _
                    = "Rome", "Italian", CityName = "Paris", "French")
End Function

Earlier Post Link References:

• Top N Records in Query
• Attachment field in Access2007
• Embedded Macros in Access2007
• Attachment Field in Access2007
• PrimaryKey usage with many fields