|
IIF vs CHOOSE vs SWITCH
Compare the IIf(), Choose(), and Switch() functions used in Microsoft Access expressions, and in VBA. They each give you the ability to evaluate a condition and return the desired result.
|
|
Compare the IIf(), Choose(), and Switch() functions used in Microsoft Access expressions, and in VBA. They each give you the ability to evaluate a condition and return the desired result.
Download a zipped Access ACCDB file with a form to experiment with IIf, Choose, and Switch. IIfChooseSwitch_s4p_ACCDB.zip
Remember to unblock the ZIP file, aka remove Mark of the Web, before extracting the database file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm
IIf() evaluates a condition and returns a value. If the condition is true, the true-part is returned. If the condition is false, the false-part is returned.
IIf( expr, true-part, false-part )
IIf is short for "Immediate If" and is the most common way to branch. When used in an Access database object expression such as in a control source for a control on a form or report, or a calculated field in a query or SQL statement, only the applicable part, true-part or false-part, is evaluated. All arguments can be static values or expressions.
However, when used in VBA, although only one part is returned, both parts are evaluated and must be valid. A common way to generate an error is to divide by zero. Adrian Bell says, "The IIf() function doesn't complete, but nor does it even start or get invoked. The crash occurs while the interpreter is preparing the values and before they're passed into the memory area used by the function."
Choose returns a variant from the list of choices based on the value of index: the first choice for 1; the second choice for 2; and so on. Returns Null if index is less than 1 or greater than the number of choices listed.
Choose( index, choice1, [ choice2 [, ... choiceN ] ] )
Choose is useful when the index represents an option group value. EVERY choice in the list is evaluated and must be valid. Index may not be Null. If index results in a fraction, it is rounded to the nearest whole number.
Switch evaluates pairs of expressions and values and returns a variant associated with the first expression in the list that is True. Returns Null if none of the expressions is True.
Switch( expr1, value1, [ expr2, value2 [, ... exprN, valueN ] ] )
Switch is analogous to a Select Case block in VBA and is useful when you want to include data based on varying conditions. EVERY argument in the list is evaluated and must be valid.
To define a default value when none of the conditions are true, make the last expression True and the value whatever you want. Here is a simple example comparing MyDateTime to the Now() function.
Switch( MyDateTime<Now, "Past", MyDateTime>Now, "Future", MyDateTime=Now, "Now", True, "no date specified" )
Create a query with this SQL statement using Switch to see a list of names and object types in your database:
SELECT m.Name
, m.Type
, m.Flags
, Switch([Type]=1,"Table",
[Type]=5,"Query",
[Type]=-32768,"Form",
[Type]=-32764,"Report",
[Type]=-32766,"Macro",
[Type]=-32761,"Module",
[Type]=4,"ODBC Table",
[Type]=6,"Linked Table"
) AS ObjectType
FROM MSysObjects AS m
WHERE (((m.Type)<>3)
AND ((m.Flags)>=0)
AND ((Left([Name],1))<>"~"
AND (Left([Name],1))<>"{")
AND ((Left([Name],4))<>"MSys"))
ORDER BY m.Name;
|
|
When used in a ControlSource, expression is prefixed with =
Help: VBA IIf function
Help: VBA Choose function
Help: VBA Switch function
Help: Now function
Help: Use Access expressions
Help: Table of operators (not all valid in VBA)
Although IIf has better performance in an object expression, there are times when Choose and Switch can be very useful!
Special thanks to Adrian Bell for his help with this topic.
Thanks also to Mike Wolfe for his article on NoLongerSet where I learned that IIf can also be a statement.
~ crystal
the simplest way is best, but usually the hardest to see