Switch Function
By Richard Rost
5 years ago
Use the Switch Function as an Alternative to Nested IIFs
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?
Members
I'll 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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
IIF Function: https://599cd.com/IIF
Keywords
Microsoft Access, switch function, select case statement
Intro In this video, I will show you how to use the Switch function in Microsoft Access as an easier alternative to multiple nested Immediate If (IIf) functions. We will talk about how to simplify complex query statements by using the Switch function to handle multiple conditions, making your queries easier to read and maintain. I will walk through a real example using a sample customer table with a family size field, and demonstrate building a query that assigns descriptive labels like none, small, medium, and large based on different size values.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. My name is Richard Rost, your instructor.
In this video, I'm going to show you how to use the switch function, which is an alternative to the if function and having multiple nested if functions inside of each other.
Today's question comes from Shannon in Chicago, Illinois, one of my gold members. Shannon asks, I have a few query statements that are super long because they are if functions, immediate if, with multiple nested options. Is there any way to shorten that down so it's not so complicated?
Well yes, Shannon, if is a great function if you only have one, maybe two options to check. But if you've got more than that - three, four, five different conditions - then you're better off using something called the switch function. Let me show you how that works.
Now, if you're not familiar with the if function, immediate if, it's basically used to do an if-then statement in one single function. I've got different videos on it on my website and on my YouTube channel. I'll put some links below. Go check those out first if you've never used if.
Basically, you give it some test, some condition, and then the value if it's true or the value if it's false. For example, if you want to say in a particular query field, if the size - whatever my size field is - equals zero, then put the word none here, otherwise put the word some there. You've got some, your value isn't zero.
If you've got multiple options to check, it gets more confusing. For example, if size is zero, put none; otherwise, then we have another if function nested inside the first one. The nested if function says if the size is less than five, put small there, otherwise put large. So if it's zero, it'll be none. If it's two, it'll be small. Otherwise, if it's 15, it'll be large.
As you can see, as we get bigger, it gets even more complicated. Zero is none, less than five is small, less than ten is medium, and then ten and larger is large. So if you have four, five, six different nested if functions, it can start getting hard to understand.
Now, the switch function pretty much works the same way, but it's just a whole lot easier to read and to write. Here it is. If you test one, then value one; test two, value two, and so on. You can add pretty much as many of these as you want. I think the maximum length of the entire function can't be more than 255 characters, so that's pretty big.
So, switch: size equals zero, none; size is less than five, small; size less than ten, medium; size greater than or equal to ten, large. Make sure your last condition takes into effect all of them. And of course, we're assuming here positive numbers. You've got a validation rule that doesn't let the user put in something less than zero.
As you can see, it's a whole lot easier to write and to read than multiple nested if functions. Let's see how this works in our database.
All right, here's my basic customer template. This is probably my free blank template. You can download it from my website, by the way. I'll put links down below. Look for the blank customer database template. There's a fully blank copy and there's one that has customer data in it too.
Real simple customer table. I have four customers in here, and I've got a field in here called family size. I've got zero, two, four, and twelve.
Let's do a simple query based on family size. Create, create design. Let's close that, bring in my customer T and close that.
Let's bring in the family size field. And right here, we'll put our switch statement. We'll make another field - we'll just call it x, that's fine. This is going to be switch: open parentheses, family size equals zero, comma none; comma, family size less than five, comma small; comma, family size less than ten, comma medium; comma, family size greater than or equal to ten, comma large. That's it, one function.
Go ahead and run it. And there you go: small, none, large, right there.
So that is the switch function. As you can see, that was a whole lot easier than writing a big complicated if function with multiple nested. You need, what, four - one, two, three, four - different if functions to handle that.
Now, if it's something that you're going to be using, this switch statement in a bunch of different places, let's say you have to make this family size calculation in a couple of different forms, a few different queries, maybe your report or two, it'd be nice to have one single function called family size that you could call from anywhere, especially if your criteria change later, if they change a medium family from five to three.
In the extended cut for members, I show you how to set up a global module. We're going to call it the family size mod. We'll make a function called family size and then we'll use something called a select case statement, which basically mimics the switch function we just learned. That's in the extended cut for members.
How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the main advantage of the switch function over using multiple nested if functions?
A. It allows for handling only two conditions.
B. It is easier to read and write when dealing with many conditions.
C. It can only be used in forms, not queries.
D. It always runs faster than if functions.
Q2. What problem did Shannon from Chicago want to solve?
A. Slow database performance
B. Complicated queries due to multiple nested if functions
C. Limited number of fields in a table
D. Difficulty exporting data to Excel
Q3. What is the basic syntax of the switch function described in the video?
A. switch(condition, value, else)
B. switch(test1, value1, test2, value2, ...)
C. switch(if, then, else)
D. switch(value1 = test1, value2 = test2)
Q4. Why might using multiple nested if functions become difficult?
A. They are not supported in Access
B. They increase calculation errors
C. They become harder to read and understand as you add more conditions
D. They cannot be used in queries
Q5. In the example provided, what family size value would result in the word 'small' using the switch function?
A. 0
B. 4
C. 12
D. 15
Q6. According to the video, what is the maximum length of the switch function in characters?
A. 128
B. 512
C. 255
D. 1024
Q7. How can you make a reusable family size calculation available in different queries, forms, and reports?
A. By writing the switch logic each time manually
B. By creating a new table for each calculation
C. By creating a global module with a function
D. By using a macro
Q8. What does the select case statement do in a VBA module according to the extended cut?
A. It creates a button for calculations
B. It mimics the logic of the switch function
C. It exports data to Excel
D. It sorts query results
Q9. Where can you find the free blank customer database template mentioned in the video?
A. In Microsoft Access by default
B. On Richard Rost's website
C. As part of Windows installation
D. From Microsoft support
Q10. What is required to access the extended cut of TechHelp videos?
A. Nothing, it is always free
B. Sending an email request
C. Becoming a silver member or higher
D. Purchasing the template
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how to use the Switch function in Microsoft Access as a more efficient alternative to using multiple nested If statements. This topic came up thanks to a question about making query statements less complicated when you have several conditions to check.
I want to start by pointing out that if you're only dealing with one or maybe two conditions, the If function (or IIf, immediate If) works just fine. It lets you compare a condition and return one value if it's true, and something else if it's false. For example, you can use it to show 'None' if your Size field is zero, or 'Some' if the value is anything else. For simple situations like this, IIf is quick and simple.
However, once you get three, four, or more conditions, the statements quickly become long and difficult to read. For example, you could have a situation where you need to classify size into 'None' for zero, 'Small' for less than five, 'Medium' for less than ten, and 'Large' for ten and above. Writing this out with a series of nested If statements quickly gets confusing and can be hard to troubleshoot or modify later.
This is where the Switch function really shines. Switch lets you write all of your conditions in a single, straightforward function call. You just pair up each condition with its result: condition one with value one, condition two with value two, and so on. You can keep adding as many pairs as necessary, up to the maximum function length allowed (which is 255 characters).
For the family size example, you would simply lay out your pairs: if family size is zero, then 'None'; if it's less than five, 'Small'; if less than ten, 'Medium'; otherwise, 'Large'. This structure is far more readable and much easier to maintain compared to nested Ifs.
In my demonstration, I used a simple customer table with a 'FamilySize' field. The table included values like zero, two, four, and twelve. I then built a query, brought in this family size field, and added a new calculated field that uses the Switch function to assign the category 'None,' 'Small,' 'Medium,' or 'Large' based on the family size value. Running the query showed correct results and you can see how easy this method is to implement and understand.
Now, let's take this a step further. If you need to use this family size classification in several places throughout your database—maybe in forms, other queries, or reports—it can be tedious and error-prone to copy and paste the same Switch function everywhere. Plus, if you later want to change your rules (for example, redefine what counts as 'Medium'), you would have to update every location.
In the Extended Cut for members, I go further and demonstrate how to set up a global module. In this module, you can create a single function—let's call it 'FamilySize'—that you can call from anywhere in your database. This function uses a Select Case statement, which accomplishes the same task as the Switch function but centralizes the logic. This way, if your criteria ever change, you only need to update your code in one place.
TechHelp videos will always be free, so you can continue to improve your Access skills at no cost. For those who are interested in going deeper, becoming a member provides access to my extended cut videos and additional perks, depending on the membership level.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Introduction to the switch function in Access
Comparison of switch function vs. nested if functions
Syntax and structure of the switch function
Creating a query using the switch function
Applying the switch function to a table field
Setting multiple conditions within the switch statement
Demonstrating output of the switch function in a query |
