Choose
By Richard Rost
6 months ago
Using the Choose Function for Value Selection In this Microsoft Access tutorial, I will show you how to use the Choose function in your queries to return different values based on an index, such as assigning category names depending on numbers or displaying star ratings. We will also compare the Choose function with other options like the If and Switch functions, and go over practical examples like fiscal quarter sorting, weekday codes, and customer order sizes. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesLinksRecommended CoursesNotes- Choose(N, "★☆☆☆☆", "★★☆☆☆", "★★★☆☆", "★★★★☆", "★★★★★")
Keywords TechHelp Access, Choose function, calculated query fields, If function, DatePart function, fiscal quarter sorting, quarter renumbering, CustomerT, FamilySize, order size categories, Shift-F2 zoom, North South East West example, weekday abbreviation, Status ID mapping, Unicode rating stars, Switch function, inequalities, string functions, logical functions, math functions, type conversion, date time functions, aggregate functions, domain aggregate functions, financial functions
Subscribe to Choose
Get notifications when this page is updated
Transcript
Today we're going to talk about the Choose function and how to use the Choose function in your queries in Microsoft Access. Today is for beginners. You don't have to know much to go over today's video. You should know what calculated query fields are. That's about it. And optionally, maybe the If function; it's basically an if-then function. And maybe the DatePart function. These are things I am going to show you in the video, but you don't have to know how If and DatePart work to get it. So let's just see what I am talking about.
In yesterday's video, which is on fiscal sorting, you can sort based on your fiscal quarter. So if your fiscal year starts in October, then you want those to sort first. I used a little trick to change the quarter. What I basically did was I got q, which is the DatePart quarter of a particular date. So it's going to give you a number from one to four. Then I used an If function to say, well, if q equals one, then I want to make that the fourth quarter. Otherwise, make it q minus one. So quarter two became quarter one, quarter three became quarter two, quarter four became quarter three, and quarter one becomes quarter four. So it flips it. You can sort properly by that method.
In the forums, one of my students, Stanley, said he got his quarters using the Choose function, like this. Then I realized, oh yeah, that does work really well for this. It's another option, and I haven't done a video on the Choose function yet. So guess what? That means it's time for a video on the Choose function.
Basically, Stanley's method with the Choose function is pretty much the same thing. You take quarter; we are both getting the quarter of the current date the same way with the DatePart function. So q is the quarter, and what Choose does is it looks at this value, this number, and then it picks from this list of options. So it's doing the same thing. If q happens to be one, it gives you the first option. If q is two, it gives you the second option, and so on down the line.
Let's see how this works in my TechHelp free template. Let's say I have a customer table here. Let's say that we're going to use this FamilySize value here. Let's pretend it's an order size. We want to give it either small, medium, large, extra large, double extra large, and so on based on that number. So one will be small, two will be medium, three will be large, four will be extra large, and so on.
Could you put these values in a table? Yes, you could, but this is one of those things where those values never change, and it's the same everywhere, and you don't ever plan on changing it. Sure, you can use the Choose function.
So I'll make a query: Create, Query Design. We don't need all this, so I'm going to close the property sheet. I'm going to close the Add Tables. I like to get my Add Tables over here from the navigation pane. So I'm going to bring over the CustomerT and I'm just going to bring in that FamilySize field.
Now, so I don't have to always refer to "what is FamilySize," I'm going to give it an alias. We're just going to call it s. s is FamilySize. I will zoom in so you can see it: Shift-F2. The voting on yesterday's video - everyone said they liked the Shift-F2 zoom better than the Windows Magnifier zoom because the Windows Magnifier can get kind of blurry. So I'll keep using this moving forward. This is what I like too.
Now, I can just refer to that field as s. It doesn't matter what it is. Here I'm going to make my order size based on whatever the value in s is. Again, I'm going to zoom in.
We're going to call this guy OrderSize: Choose. You don't have to type it in all caps; I just do it for emphasis. The first thing is what field has the value that you want to use for your Choose function? Well, that's s. We just made that.
Now if Choose is equal to one, I'm going to put in here the word "Small," comma. If Choose equals two, I'm going to put in here "Medium." If it's three, "Large," if it's four, "X-Large." If it's five, I just do "XL" or "XXL" and then "XXXL" and so on.
Choose, like I said, is one-based, so the first item has to be one. If you have zeros or any numbers larger than the number of items in this list, Choose will return a null value. Make sure you have enough options in here to cover all the possible values.
Now, when I run this bad boy, there you go. That's how Choose works. It's good for small lists, like the thing we did with the fiscal year. That's great. Small, medium, large - that kind of stuff is fine. Any set lists that you know aren't going to change.
Some other examples: North, South, East, and West. Sure, not a problem. If you give it a three, it's going to return East. Again, if this is something that's consistent throughout the entire database, you're never going to change it, you're never going to add or delete more items, go with this. That's just fine.
Here's one for a single character weekday. You see this a lot in academia. I remember this back from high school. R is Thursday. So you have your Weekday function. The Weekday function takes a date and returns a number from 1 to 7. If it's 1, it's U for Sunday: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.
Status IDs: again, Pending, Approved, Rejected, Archived. Do you need to put this stuff into a table? That's kind of up to you. If it's always these four, it's never anything else, and you don't want to run around in your database and have to change or add this stuff everywhere, then, sure, go ahead and throw it right in here, hard-coded.
Here's one of my favorite things: a rating system. These are little Unicode characters. One star, two stars. So if the rating value is one, you get one star; two, you get two stars; three star, four star, five star. These are little Unicode characters. I've got another video coming up to teach you how to actually use these. That's going to be pretty cool. But you can copy this stuff here. I'll just copy these right at this PowerPoint slide. Copy.
All right, come back over here, Design View, and I'll just do this here. I'll get rid of this. Paste it in. Access supports most Unicode character - Unicode, not "Unicorn" - characters. I think I just watched a fan-made video on YouTube for the Dungeons and Dragons cartoon. They made another episode. It was really pretty cool. So I got Unicorn on the mind from Unicode.
There you go; there's that. Hit OK. I'm going to save this as my ChooseQuery. Now, when I run it, what do you get? There you go. You get your five-star rating. See? And it's picking the right one out of the Choose function. I'm going to talk about more of this in my upcoming special characters video. Notice zeros come back null. Eleven comes back null because there is nothing in there.
If you send it a null value, you're going to get an error message because it can't take a null. So what you could do is you could say this has to be "Is Not Null," and there you go. You will get those null values. Or you could always use Nz if you want to.
So, there you go. That's the Choose function. That's just another one of those great toolbox functions to have in your box of tools.
I do cover Choose in more detail in my Access Expert Level 25 class in my full course. I have covered pretty much every function that you can think of that's included in Access and VBA. I just haven't made TechHelp videos for them all because, honestly, Choose isn't a function that I use on a regular basis, but it is handy in certain circumstances.
There are other functions that are similar. For example, I use If a lot, nested Ifs once in a while, if it's not too many options. Switch is another good option. Switch is better for inequalities. I have a whole separate video on the Switch function.
So here is our Choose function. Let's call this guy Rating. I'll do it again with the Switch function so you can see the difference. I'm going to copy this. I will come over here and paste that in. Let's zoom in: Shift-F2.
Let's say this would be the rating with Switch. This you'd have to say is going to be: =Switch. The way Switch works is first you'd say s = 1, then you get your value, after that another item, s = 2 and then a value, and then s = 3 and then a value. It's an item, and then what you want for that item. An equality or inequality also. s = 4, and then you get this guy, and then s = 5, and you get that guy. That's how Switch works.
If I run it, it works; you get the same values. But as you can see, in this particular case, it's a lot more work typing all that in. This works better with, as I said, inequalities. You can do grades. You could say if the grade is greater than 90, then give it an A; if it's greater than 80, give it a B, and so on. So Switch I use mostly for inequalities.
Choose is really nice if you just have one value and it's IDs, for example, one, two, three, four, five. Check out that video. Like I said, in my full course I do a comprehensive guide to Access functions. I cover all the different functions. There's the string functions, the logical functions, the math functions - yes, even the trig functions. I've got an example with trig in here. The type conversion functions. Date/time functions; we've got a lot of date/time functions. There are tons of date/time functions. Aggregate functions. Domain aggregate functions. You name it, there is lots of stuff in here. Financial functions; loan payment calculators, the most popular one.
What other kind of functions? Let me get into charting and stuff. So, yes, there's all kinds of cool stuff in my lessons. Check them out. Links down below.
That's going to do it. That's your TechHelp video for today, folks. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mention in the video can be found in the description text below the video. Just click on that Show More link. YouTube is pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. Did I mention it is completely free? And if you like level one, level two is just one dollar. That's it, and it's free for members of my YouTube channel at any level.Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks.
Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members also get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, from every subject, and you get one free advanced or developer class every month after finishing the expert series.
You can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com.
I hope you enjoyed. I hope you learned something today. Live long and prosper my friends. I'll see you next time.
TOPICS:
Using the Choose function in Access queries
Assigning text values based on numeric fields with Choose
Creating calculated fields with aliases in queries
Mapping numbers to labels like Small Medium Large using Choose
Handling out-of-range and null values with Choose
Using Choose for fixed option lists in queries
Applying Choose for direction labels like North South East West
Generating weekday abbreviations with Choose and Weekday
Creating rating systems with Unicode characters and Choose
Comparing Choose and Switch functions for value assignment
Implementing Switch function for equality and inequality cases
Deciding when to use Choose versus a lookup table
Handling null input values with Choose and Nz
COMMERCIAL:
In today's video, we're learning about the Choose function in Microsoft Access and how you can use it in your queries to easily return values based on a number, like showing Small, Medium, Large, or even star ratings and compass directions from a single field. I will show you step-by-step how Choose compares to other functions like If and Switch, when to use it, and how to handle lists that never change. You'll pick up tips for working with calculated query fields, see real examples, and get a beginner-friendly look at why Choose is a handy function to have. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary purpose of the Choose function in Microsoft Access queries?
A. To select a value from a list based on a numeric index
B. To generate random numbers for testing data
C. To sort records in a specific order
D. To create calculated dates in queries
Q2. When is it generally appropriate to use the Choose function instead of a lookup table?
A. When you have a short, fixed list of options that will not change
B. When your list frequently changes and requires updates
C. When you have thousands of unique values
D. When you need to perform complex calculations
Q3. How does the Choose function determine which item to return?
A. It uses the provided numeric value to select the corresponding item from the list
B. It always returns the last item in the list
C. It selects an item at random each time the query runs
D. It compares each item in the list to a text value
Q4. What happens if the index passed to the Choose function is zero or larger than the number of items in the list?
A. The function returns a null value
B. The function wraps around to the first item in the list
C. The function returns zero
D. The function displays an error message
Q5. In the example with FamilySize, how did the video suggest using Choose to label order sizes?
A. By mapping numeric values to size names, such as small, medium, large
B. By multiplying each value by two
C. By using the modulo operator to assign sizes
D. By creating a separate table for each size
Q6. Which type of values makes the Choose function especially useful?
A. Consistent, unchanging sets like North, South, East, West
B. Long lists of frequently updated items
C. Dynamic, user-generated data
D. High-precision numeric values
Q7. What is a limitation of the Choose function as discussed in the video?
A. Does not work with non-sequential numeric values
B. Only works with text fields
C. Requires connection to the internet
D. Only available in macros, not queries
Q8. What type of value does the Weekday function return, as shown in the video example?
A. A number from 1 to 7 representing the day of the week
B. The text name of the weekday
C. The month number
D. The fiscal quarter
Q9. What alternative function did the video present for conditions with inequalities, such as grading systems?
A. Switch function
B. IfError function
C. Concat function
D. Lookup function
Q10. Why might you choose to use the Switch function instead of Choose for some use cases?
A. Switch makes it easier to handle ranges and inequalities
B. Switch works better with Unicode characters
C. Switch can only return numeric values
D. Switch automatically sorts your data
Q11. What occurs if you provide a null value to the field used by Choose?
A. The function will generate an error message
B. The function will return the first item in the list
C. The function will output an empty string
D. The function will skip that record
Q12. When should you use a lookup table instead of Choose for storing values?
A. When the set of options may change in the future
B. When the values are always the same and never change
C. When the values are related to dates and times
D. When the list is shorter than three items
Q13. Which of the following scenarios would NOT be a good fit for using the Choose function?
A. Mapping customer family sizes to order labels that never change
B. Assigning direction names to a fixed set of IDs
C. Translating dynamic, user-generated survey responses to codes
D. Creating a 5-star rating system with set icons
Q14. According to the video, what is one advantage of Choose over nested If statements for this type of mapping?
A. Choose is simpler and more compact for mapping IDs to set values
B. Choose can compare two fields at once
C. Choose automatically reverses mapped values
D. Choose handles ranges and inequalities better
Q15. Which Access function is suggested as being particularly good for handling scenarios where you have to compare values to ranges or inequalities (like grading)?
A. Switch function
B. Choose function
C. Nz function
D. Weekday function
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-C; 14-A; 15-A
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 TechHelp tutorial from Access Learning Zone is all about the Choose function in Microsoft Access, with a focus on how you can utilize it in your queries. This lesson is intended for beginners, so you do not need any advanced knowledge to follow along. The only basics you should be familiar with are calculated query fields. It might help if you know about the If function or the DatePart function, but I will show examples so you do not have to know them ahead of time.
Recently, in a tutorial about fiscal sorting, I explained how you could sort data by fiscal quarter if your fiscal year starts in October. In that example, I used the DatePart function to determine the quarter for any given date. Then, I used the If function to shift the quarters so that quarter one became quarter four, two became one, and so on, to align with the fiscal calendar. This allows you to sort your quarters the way you want for your business needs.
While discussing this topic in the forums, one of my students suggested accomplishing the same result by using the Choose function. His approach worked quite well and reminded me that the Choose function is a solid alternative for these scenarios. This inspired me to create a dedicated lesson on how to use the Choose function in Microsoft Access.
The Choose function operates by taking a number and using it to select one item from a list of values. For instance, if you have a value for the quarter (from the DatePart function), Choose can look at that value and pick the corresponding label or result from your predefined list. For example, if the value is 1, it takes the first value; if it is 2, it takes the second, and so on.
Let us look at an example using my TechHelp free template. Suppose you have a table of customers, and each customer has a FamilySize value. For this example, imagine FamilySize represents an order size, and you want to label these orders as "small", "medium", "large", "extra large", and so forth, depending on the FamilySize number. One would map to "small", two to "medium", three to "large", and the pattern continues.
You could store all these values in a separate table, but for a small fixed list that never changes, the Choose function is perfect. I built a query in Access, added the customer table, and included just the FamilySize field. To make this field easier to reference in the query, I gave it an alias, calling it "s". That way, I can simply refer to the value as s for the rest of the query.
Using Choose, I can create a calculated field called OrderSize. For this, I specify s as the index for the Choose function and then provide the list of possible output labels, such as "Small", "Medium", "Large", "X-Large", "XXL", "XXXL", and so on. Keep in mind that Choose is one-based, so the first item in your list corresponds to 1. If s is zero or higher than the number of items in your list, Choose will return a null value, so make sure you include enough list items to cover all potential values.
Once you run this query, you will see how it assigns the correct label based on the value of FamilySize. This is ideal for cases where you know your list will never change - like for order sizes, cardinal directions, or predefined status values.
Other good examples for using Choose include mapping values for compass directions (such as North, South, East, West), or for single-letter weekday abbreviations. In academic contexts, days of the week are sometimes abbreviated as U for Sunday, M for Monday, T for Tuesday, and so on – you could use Choose to handle these mappings as well.
A fun example is a rating system using Unicode star characters. If you have a rating value from one to five, you can display one to five star symbols accordingly. Since Access supports most Unicode characters, you can use Choose to output the correct number of stars. Just make sure you do not confuse Unicode with "Unicorn" – a little joke from a recent video I watched. When running the query, you can see the stars displayed. If you pass in zero or a number higher than the last defined value, Choose returns null. If you send a null value to Choose, you get an error, so you should either filter those out or use Nz to handle them.
That is how the Choose function works. It is a simple but powerful tool for handling fixed lists where you do not expect changes. I cover Choose in more detail in my Access Expert Level 25 class, where I include most of the functions you might need in Access and VBA. While Choose is not one I use daily, it is useful in specific scenarios.
There are similar functions you might consider. For example, If and nested If statements work well for a handful of options. For more complex situations, especially those involving inequalities, the Switch function is a better choice. I have a separate lesson on the Switch function, which is more flexible for dynamic conditions, such as assigning grades (if the score is above 90 assign an A, above 80 assign a B, and so forth).
In side-by-side examples, you will find that using Switch for a simple one-to-one mapping like with rating stars involves more typing, while Choose remains concise and easy to read. However, Switch shines when you need to evaluate ranges or conditions rather than just picking from a list by position.
Throughout my Access courses, I provide comprehensive overviews of many Access functions, including logical, string, math, type conversion, date/time, aggregate, domain, and even financial functions. These cover a broad array of practical uses, from creating calculators to analyzing data.
That is everything for today's tutorial on the Choose function. If you want to see step-by-step demonstrations of everything discussed here, you can find the complete video tutorial on my website at the link below.
Live long and prosper, my friends.
Topic List
Using the Choose function in Access queries
Assigning text values based on numeric fields with Choose
Creating calculated fields with aliases in queries
Mapping numbers to labels like Small Medium Large using Choose
Handling out-of-range and null values with Choose
Using Choose for fixed option lists in queries
Applying Choose for direction labels like North South East West
Generating weekday abbreviations with Choose and Weekday
Creating rating systems with Unicode characters and Choose
Comparing Choose and Switch functions for value assignment
Implementing Switch function for equality and inequality cases
Deciding when to use Choose versus a lookup table
Handling null input values with Choose and Nz
|
