IIF Function
By Richard Rost
5 years ago
IIF Function - Immediate IF in Microsoft Access
In this video, I'm going to show you how to use the IIF function, which stands for Immediate IF, to display one value if a condition evaluates to TRUE and a different value if it evaluates to FALSE. This is like having an If/Then statement in your queries and forms. I'll show you how to display "Past Due" if an order is overdue. We'll display "High Credit" on credit limits over $1000. We'll make a query showing the country each customer is in, substituting "United States" for NULL values.
Jayvon from Oakland, California (a Gold Member) asks: I have a form that I would like to display "past due" next to the due date. Is there any way I could do this without programming? I know you say VBA is easy, and I'm learning with your lessons, but I need this quick. Thanks!
Members
I'll show you how to use Nested IIF functions to determine a student's letter grade from a test score (90=A, 80=B, 70=C, etc.). I'll remind you about the Switch function which is better. I'll show you how to use the IIF function in your VB code. We'll make "Past Due" change to Red, "Due Soon" in Yellow, and "Current" in Green.
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
Subscribe to IIF Function
Get notifications when this page is updated
Intro In this video, I will show you how to use the Immediate IF (IIF) function in Microsoft Access. We will learn how to display "past due" next to a due date if an order is overdue, create a query to show "High Credit" for customers with a credit limit of $1,000 or more, and display "United States" in place of blank country fields using IIF in a query. I will walk you through these examples step-by-step, including how to set them up on forms and in queries without using VBA.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to use the Immediate IF function in Microsoft Access. That's the IF function, IIF.
First, we'll make the words "past due" appear on an order next to the due date if the order is in fact past due. We'll compare the due date of the order to today's date. We'll make a query to display "High Credit" if someone's credit limit is $1,000 or more; otherwise, it will display the word "Normal." And then finally, we'll look at the customers and the country they're in, and if that country field is blank, we'll use the IF function to make it display "United States."
Today's question comes from Javan in Oakland, California, one of my Gold members. Javan asks, I've got a form that I would like to display "past due" next to the due date. Is there any way I could do this without programming? I know you say VBA is easy and I'm learning with your lessons, but I need this quick. Thanks.
Yeah, Javan, there are all kinds of things you can do with VBA once you learn it. But let me show you how to do it first without VBA so you can get your job done. Then I'll show you some other stuff in the extended cut. But for now, let's take a look at this.
Here I am in my TechHelp free template. You can download a copy of this template from my website. It's absolutely free. There's a link down below in the description. Go grab it if you want to.
In here, we've got a basic customer form and, in the customer form, you'll see there are some orders as an order form. I only have an order date in here, but let's say we have a due date. Let's add that.
Let's go back to the order table, design view. There's order date. Come down here and put due date. Date/time. Let's go back to the order form and put the due date up here. I'm going to slide this over here, go to design view, open it up a little bit, and slide this down to the subform.
We'll move these guys. Whoops, I just grabbed that by accident. Let's select these and drag them down. Let's copy and paste the order date. Watch this--copy, paste. See that? Slide it right there. We'll change this to due date, and open this guy's properties up. Go to all and change this control source to due date, and change the name also, so that's due date.
Let's save this. See what we've got? Save it, Control+S. Close it down. Open it back up again. All right, we've got a due date field right here.
Now, we don't have any values in our due dates. It's a new field, so I have to put some values in here. Today is February 8, 2021. So I'm going to put 2/15 in here. That's not due yet.
Now let's make a little room over here. Design view. I'm just going to shrink this guy up a little bit, move the invoice button over here. I'm going to put another field right here that will either show "past due" or it'll show nothing. So I'm going to put an unbound text box here. Go up to your ribbon, grab a text box, and drop it right next to that. You can delete that label, get rid of the label, and slide this right up here.
Now, unbound means this guy is not bound to a field in the underlying table. There's nothing that's going to be loading this guy with data. We're going to put the data in there ourselves. Double click on this. Go to the control source.
We're going to use something called the Immediate IF function. IF is basically an "if then" statement, which you get from Visual Basic--if this, then do that--but it's all in one little function, just like SUM or AVERAGE or random simple functions that we learn in Excel. It takes three bits of information.
The first thing is: what is your condition? For example, X is greater than five, or the due date is less than or equal to today. The second thing is what to put for the value if it's true, and the third thing is what to put for the value if it's false.
So here's what it looks like: =IIF([DueDate]<Date(),"Past Due",""). Let me zoom in so you can see this better. IIF(open parentheses), if the due date is less than today's date (Date()), comma, what to put in this box if that's true ("Past Due"), comma, otherwise what to put if it's false (an empty string: ""). Close your parentheses. Hit OK.
Save it, close it, and then open it back up again. All right, nothing's in there. Now let's change it. Let's put 2/1 in there, which is before today. Look at that; it says "Past Due." See that? Nice and simple.
Go to a different order. Go over here. Let's go to Jim Kirk--orders. He's got something in here. Let's put in here 3/1. Nothing shows up. But if this was due on 1/1, look at that. "Past Due."
That's how the Immediate IF function works. You can make this look fancier. You can put some conditional formatting on it so it turns red, whatever you want. If you're not familiar with conditional formatting, I've got other videos on that. I'll put links down below in the link section.
You can also use the IF function in queries. You can use them in queries, forms, and reports--and in your VBA code.
One value that we have in our table is a credit limit. For example, $5,000 for me. Let's use a query and make a field say "High Credit" if that value is over $1,000. Go back to create, query design. Bring in my customer table. Bring in the customer ID, first name, last name, and let's find that credit limit. There it is right there. So far, this is what I've got.
Let's put a couple of other credit limits in here: 250, and then 75. Now I want another column over here that will say "High Credit" if this value is over $1,000.
Go back to design view. Come right down here. We're going to say "IsHigh" or whatever you want to call it. That's called a field alias. If you've never worked with queries before, I've got other query design videos you should watch first. Watch that and then come back here. Look down below for the query design video in the link section. I cover a lot of queries in my Access Beginner 1 class--free, three hours; go watch it.
I'm going to zoom in again, Shift+F2. "IsHigh:" We're going to say IIF([CreditLimit]>=1000,"High Credit","Normal"). Hit OK, save it. Let's call this "My Customer Credit Q," and then let's run it. There we go: "High Credit" or "Normal." This will update whenever this field updates, too.
That value is not stored in the field. This is not stored in the table. This is a calculated query field. Again, I have videos on calculated query fields; go watch that.
One thing that I like to do in my databases, if you look at the customer table: I'm in the United States, so I usually leave the country field blank or null if the customer is in the United States. What if you want a query that actually shows a list of countries that your customers are in? We can use the Immediate IF and substitute null for "United States."
Create another query, query design, customers again. Bring in first name, last name, bring in the country. This is what we've got so far. Only one person in a different country. Let's say Jim Kirk is from Canada.
Let's go back to design view. Over here, let's call this "CountryString" or whatever you want to call it. Let me zoom in for you, Shift+F2. CountryString: IIF(IsNull([Country]),"United States",[Country]). You can use a field in here as well, or a calculation, or even another IF statement. You can nest these inside of each other. If you've got three, four, five conditions, I'll show that in the extended cut.
Hit OK, save it. Let's call this "Customer Country Q." Run it. Look at that--"United States" or the country that the customer is actually in. Pretty cool stuff. That's the Immediate IF, and I've just scratched the surface. There is a lot you can do with Immediate IF.
I cover the Immediate IF function in detail in my Microsoft Access Expert Level 8 class. I'll put a link to this down in the description below the video.
Want to learn more about the IF function? Take my Access Expert Level 8 class, but I've got an extended cut available of this video, where I cover more for the members--Silver members and up.
First, we'll cover Nested IF function--that's an IF function inside of another IF function, inside of another IF function. We can use that to do the famous student letter grade problem. I do this in my Excel class and in my Access classes. You have a test score or a final grade or whatever, and you want to convert that over to a letter grade. We'll use the IF function to do that.
I'll give you a review of the SWITCH function, which is actually easier than nested IFs, but it has its own set of limitations and benefits, too. Then we'll take the IF function into our VB code. OK, Javan, I'll give you a little bit of VB so you can see what's going on. We'll go a little bit further into the VB just to get your feet wet. I'll show you how you can actually colorize that field: "Past Due," "Soon," and "Current."
That's all covered in the extended cut. Silver members and up get access to all of my extended cut videos.
How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available.
Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use.
Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.
If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted. So if you'd like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1, and it's also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is the purpose of the Immediate IF (IIF) function in Microsoft Access?
A. To perform calculations across multiple tables
B. To create a conditional statement that returns one value if true, and another if false
C. To import data from Excel
D. To permanently change values in a table
Q2. In Access, how many arguments does the IIF function require?
A. One
B. Two
C. Three
D. Four
Q3. Which of the following is a correct example of using the IIF function to display "Past Due" if the due date is before today?
A. IIF([DueDate]>Date(),"Past Due","")
B. IIF([DueDate]=Date(),"Past Due","")
C. IIF([DueDate]<Date(),"Past Due","")
D. IIF([DueDate]<>Date(),"Past Due","")
Q4. What does it mean when a text box on a form is described as "unbound"?
A. It automatically shows primary key values
B. It is connected to a field in a different table
C. It is not connected to any field in the underlying table
D. It is only visible in datasheet view
Q5. Where can the IIF function be used in Microsoft Access?
A. Only in tables
B. Only in forms
C. In queries, forms, and reports
D. Only in VBA code
Q6. In a query, how would you display "High Credit" if a customer's credit limit is $1,000 or greater, otherwise display "Normal"?
A. IIF([CreditLimit]<=1000,"High Credit","Normal")
B. IIF([CreditLimit]>1000,"High Credit","Normal")
C. IIF([CreditLimit]>=1000,"High Credit","Normal")
D. IIF([CreditLimit]=1000,"Normal","High Credit")
Q7. What is a "field alias" in the context of Access queries?
A. A nickname for a query
B. The name used for a calculated column in a query
C. A reference to another table
D. The primary key field name
Q8. If you want to substitute a blank country field with "United States" in a query, which function combination did the video demonstrate?
A. IIF([Country]="","United States",[Country])
B. IIF(IsNull([Country]),"United States",[Country])
C. IIF([Country]=Null,"United States",[Country])
D. IF([Country] IsBlank,"United States",[Country])
Q9. What happens to values generated by calculated fields using IIF in queries?
A. They are permanently stored in the table
B. They are lost when closing the query
C. They are calculated on-the-fly and not stored in the underlying table
D. They automatically update all database records
Q10. What is "conditional formatting" in the context of Access forms?
A. Changing database structure based on conditions
B. Formatting records to be deleted automatically
C. Visually changing the appearance of controls based on certain criteria
D. Exporting reports with special colors
Q11. What is a "nested IF"?
A. Using multiple tables in the same query
B. Placing one IF function inside another IF function
C. Linking forms with subforms
D. Using a query inside a report
Q12. What alternative to nested IFs was briefly mentioned in the video?
A. CHOOSE function
B. SWITCH function
C. SUMIF function
D. COUNTIF function
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-C; 10-C; 11-B; 12-B
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 focuses on using the Immediate IF function, or IIF, in Microsoft Access. I am your instructor, Richard Rost, and I'll guide you through several practical examples of how IIF can help display different results based on conditions in your database.
To start, I'll demonstrate how to make the phrase "past due" appear next to the due date of an order if the order is indeed past due. The key here is to compare the due date of an order against today's date. If the order's due date is earlier than today, "past due" will be displayed; otherwise, the field remains empty. This can be done without writing any VBA code, which is ideal for those who want a quick solution.
The scenario comes from a student who wanted to show "past due" in a form without using programming. Although VBA makes such tasks very flexible, the IIF function provides a straightforward way to accomplish this directly in the form.
First, ensure the order form includes a due date field. Add the due date to both the order table and the order form if it does not exist already. Once you have the due date in place, create an unbound text box next to it on your form. This text box will display either "past due" or nothing, depending on the due date. In the control source for this text box, you'll use the Immediate IF function. The syntax takes three arguments: the condition to check, the result if true, and the result if false. For instance, the formula will check if the due date falls before today's date. If so, it displays "Past Due;" if not, it leaves the field empty.
This method is flexible and can be styled further. For example, conditional formatting can be applied to change the color of the "past due" text, which helps draw attention to overdue items.
Beyond forms, the IIF function is useful in queries as well. In another example, you might want a query to display "High Credit" if a customer's credit limit is at least $1,000 and "Normal" otherwise. When building your query, add a calculated field using the IIF function: it checks the credit limit using the condition and sets the display value accordingly. Keep in mind that this calculated value is not stored in the database; it exists only in the query results.
Another application involves handling blank or null values in the country field for customers. If your practice is to leave the country blank for domestic customers, but you want a query to display "United States" instead of a blank field, use the IIF function paired with IsNull to substitute the value. In your query, create a calculated field that checks if the country is null, and if so, returns "United States." Otherwise, it shows the actual country from the data.
This approach can be expanded further. The IIF function allows for nesting, so you can check multiple conditions in sequence. In today's extended cut, I'll cover how to use Nested IF functions, which enable you to solve more complex situations, such as converting a test score to a letter grade with multiple possible outcomes. The extended cut will also include an overview of the SWITCH function, which can be more straightforward than nested IFs, depending on your use case. Additionally, I'll show how the IF function can be applied in VBA code, including how to colorize the field to indicate different status such as "Past Due," "Soon," or "Current."
Silver members and up on my website have access to all the extended cut videos, which provide deeper instruction and more advanced examples on topics like these. You can learn more about membership levels and the additional resources they provide, from full database samples to the Code Vault and full-length courses on Access and other Microsoft products.
Remember, these free TechHelp videos will keep coming as long as you keep watching and participating. Your feedback is appreciated, so please leave a comment or question if you have one. Subscribing to my YouTube channel and joining my mailing list are great ways to stay informed about future videos and tutorials. If you have not yet tried out my free Access Level 1 course, it is available on my website and my YouTube channel and covers the basics of building databases.
If you would like your question featured in a video, visit my TechHelp page and submit your inquiry there.
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 Adding a Due Date field to an order table and form
Displaying "Past Due" for overdue orders using IIF
Setting up an unbound text box for calculated output
Writing an IIF expression comparing DueDate to today
Showing conditional text based on field values in forms
Using IIF in queries to classify credit as "High" or "Normal"
Creating a query calculated field with IIF
Setting a custom country display with IIF and IsNull
Building a query field to default country to "United States\ |
