Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
In this chapter, we're going to take a look at creating a new function called a conditional statement. Conditional statements are one of my favorite features of Access queries. They allow you to create an automated decision-making process. They're very flexible and very powerful and they can automate some complex tasks in a short amount of time. So let's dig in to see what this is all about. A conditional statement is an if-then statement. In Access, we will use the IIf function and yes, there are two I's there. That's not a typo. The first I stands for immediate, meaning that this is a function that runs inside of an SQL statement.
This is so Access can distinguish between this function and the Visual Basic If routine. The IIf function has a specific syntax that must be followed. There are three required arguments. The expression to evaluate, the piece to return if true, and a piece to return if false. The first part of our condition is the expression. Any mathematical expression that evaluates to true or false will work. The true part argument is returned if the expression is true and the false part argument is returned if the expression is false. Many of the mathematical operations that we've seen in this course can be used to construct your expressions.
We can use the operators Less Than, Greater Than, Equal. And, Or, and Not will also work. We can use any of these to build the expression that Access will evaluate. An IIf function that evaluates August sales compared to a plan or goal might look something like this. It reads is the August sales over $100,000? If it's true, they will say that the store met their sales plan and if that's false, we will say the store did not meet their sales plan. So let's see how Access would apply this in our data tables. If we take a column of data called August Sales and we plug that into our Expression, Access will evaluate the result.
So if we take Store 101 and look at their August sales, 103,588. If we plug that into our IIf statement, we'll get an expression that reads 103,588 is greater than 100,000. Well, that's true. So the result is, met sales plan. The same thing applies to Store 102. Their August sales is over 100,000, so they also met their sales plan. Now Store 103, their sales were 98,743.
If we plug that into our IIf statement, we get 98,743 is greater than 100,000. Well, that's a false statement. So Access will return below sales plan and so on down the list. So you can see that if you had to manually evaluate August sales and take each store's value and compare it to goal and then define a result, that could take you quite a long time. By using an IIf conditional function, Access can automate that process for you. We will start in the next video by building our own IIf function using the Expression Builder.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.