Using the Switch Function to Simplify Complicated Nested IIF Functions in Microsoft Access

Learn how to use the Switch function as an alternative to multiple nested IIF functions in your Microsoft Access databases.

Shannon from Chicago IL (a Gold Member) asks: I have a few query statements that are super long because they are IIF functions with multiple nested options. Is there any way to shorten that down so it's not so complicated?

BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you how to create a global function to calculate family size with a Select Case VBA statement. This is so that you don't have to copy your Switch function to multiple places.

MEMBERS ONLY VIDEO:

BECOME A MEMBER:

LINKS:

ADDITIONAL RESOURCES:

KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, switch function, select case statement

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[bunnywebit]

Quick and easy solution to my problem. Everything else I found required much more complicated SQL. Thank you for all of your videos and step-by-step walkthroughs!!

[rabidfollower]

Switch() doesn't have an "Else" portion like the If and Select Case statements do. But you can create your own Else portion by adding "True" as the last condition. E.g. Switch(cond1, value1, cond2, value2, ... True, value N). This could be important because if all conditions are false, Switch returns null, which you probably want to avoid.

[Initial_Gain]

I will try Switch out as i have a case where the nested IIF statement is working for only 2 IIFs in the 'Control Source' of a Text Box in a subform. The 3rd IIF onwards aren't working, only up to 2 IIFs work. Thank you, nice work 👍 🇲🇹

Later on I found my mistake, I needed to use AND in my nested IIF statements. Same with the Switch function. But unlike as I've read here in one of the comments, the Else didn't work with the Switch and always gave an error.

[inukpaw]

HI Rick, a question on the switch video. The criteria [familysize] <5 = "small" and [familysize] <10 = "medium" result in both being true. Which result will it give for say 3 as this is less than 5 and 10 ? or am i missing something ?

[archanaaa]

Is Ms access has scope now a days? Learning expression building is enough or SQL queries?