New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Nesting IIf functions

From: Access 2010: Queries in Depth

Video: Nesting IIf functions

In the last movie, we took a look at how we can use a conditional statement to return one of two values. We would supply an expression, and depending on whether that expression is true or false, we would get a branching decision tree. Now we can nest conditional statements together, so instead of a true part, we might evaluate to a second IIf statement, or instead of false, we might go to a second or third IIf statement that way. Now if we nest multiple conditional statements together, we can get a branching decision-making tree. This will function much like a flowchart where we'll start at the top and depending if the statement is true or false, we'll move left or right, we'll evaluate with a second expression, and again move left and right from there.

Nesting IIf functions

In the last movie, we took a look at how we can use a conditional statement to return one of two values. We would supply an expression, and depending on whether that expression is true or false, we would get a branching decision tree. Now we can nest conditional statements together, so instead of a true part, we might evaluate to a second IIf statement, or instead of false, we might go to a second or third IIf statement that way. Now if we nest multiple conditional statements together, we can get a branching decision-making tree. This will function much like a flowchart where we'll start at the top and depending if the statement is true or false, we'll move left or right, we'll evaluate with a second expression, and again move left and right from there.

At the end of the chain, we can have four expressions returned, four different values depending on how it flowed through the chart. Let's go ahead and take a look at this concept in Access and we'll apply it to a sales tax calculation. So let's create a query that decides whether or not we should be charging sales tax to our customers. Let's assume that the Two Trees Olive Oil Company has a physical presence in the state of California and Texas and any customers purchasing product in those states will need to get charged the sales tax. We can go into our Create tab and create a new query in Design view and we'll add a few tables.

We'll add the DirectCustomers table, the Orders table, and the Products table. Let's go ahead and close Show Table and we'll add our fields. From the Customers table we'll add CustID and FirstName. We'll also add the State so we can identify the state whether we need to charge tax or not. From the Products table, we need the Price. So we can find out how much tax to charge. In the fifth field, we'll build our conditional. Right-click and say Build and let's find our conditional function. We'll go to Functions > Built-In Functions > Program Flow, and double-click IIf.

So the first thing we need to do is find out if the customer lives in California. We'll write State = California. We'll go into our tables, find our customer, and grab their State. We'll type in =ca to find out whether that customer lives in California and we'll wrap that in quotation marks. If the customer lives in the state of California, we need to multiply their order total by 0.08% to get the amount of sales tax to add.

So let's go into their Products table, we'll find Price, and we'll multiply that by 0.08. So that takes care of our California customers. If the customer doesn't live in California, we need to find out whether they live in Texas. So for the dalse part, we'll start another conditional. We'll go back up to our Built-In Functions > Program Flow, and double-click IIf again. This time we want to find out if the customer lives in Texas. Go back to DirectCustomers, find their state, and we'll say =tx.

Again, remember our quotation marks. If that's true, we'll charge the Texas state sales tax at .0.05%. We'll go to Products, Price, *.05. Finally, if this expression evaluates a false, the customer does not live in Texas, we won't charge any tax at all. So we'll just enter in 0. Let's go ahead and add an alias to our column header. We could do that by clicking the very beginning and we'll type in Sales Tax followed by a colon.

So there's our completed conditional. If the customer lives in California, we'll take the price and multiply it by .08. If they don't live in California, we'll go to the next part, which asks do they live in Texas. If they do live in Texas, we'll multiply the price by .05. And if they don't live in Texas either, we'll put in a 0. Okay, let's go ahead and evaluate this expression by running our query. And here we can see how much sales tax we're going to add to each customer who lives in either the state of California, or if I scroll down we'll find a customer that lives in the State of Texas.

Let's go ahead and add a couple of more fields to finish the query. We'll go back into Design view and we'll add a calculated field here to add the sales tax to the price to get their total cost. This time I'll just right-click and go to the Zoom window and we'll type this manually. So the first thing we want is an alias that says Total Cost followed by a colon. Then we want to take the Price field from our query and we'll wrap that in square brackets, and we'll add it to our sales tax field, again wrapping it in square brackets.

So now we have the calculated field called Total Cost that will take the price and add it to the sales tax. Let' go ahead and say OK and run our query one more time. So here's the customer's final Total Cost after tax, if there is any. So that's how we can use a nested conditional statement to expand our options even further.

Show transcript

This video is part of

Image for Access 2010: Queries in Depth
Access 2010: Queries in Depth

46 video lessons · 13236 viewers

Adam Wilbert
Author

 
Expand all | Collapse all
  1. 9m 9s
    1. Welcome
      1m 10s
    2. Using the exercise files
      41s
    3. Introducing the database
      4m 29s
    4. Previewing the course
      2m 49s
  2. 17m 17s
    1. Understanding queries
      3m 31s
    2. Following naming conventions and best practices
      2m 56s
    3. Using the Query Wizard
      5m 21s
    4. Exploring the design interface
      5m 29s
  3. 26m 39s
    1. Defining criteria
      5m 40s
    2. Understanding comparison operators
      3m 19s
    3. Defining the column headers
      2m 49s
    4. Exploring the property sheet
      7m 32s
    5. Printing query results
      2m 41s
    6. Working with joins
      4m 38s
  4. 14m 14s
    1. Understanding parameter queries
      4m 27s
    2. Obtaining parameters from forms
      5m 17s
    3. Creating a combo box
      4m 30s
  5. 23m 24s
    1. Understanding the Totals field
      5m 31s
    2. Creating aggregate calculations
      3m 31s
    3. Exploring the Expression Builder interface
      4m 28s
    4. Using mathematical operators
      5m 46s
    5. Applying text functions
      4m 8s
  6. 24m 23s
    1. Understanding dates as serial numbers
      2m 42s
    2. Specifying a range of dates or times
      3m 47s
    3. Formatting dates
      4m 31s
    4. Using other Date/Time functions
      3m 47s
    5. Defining today's date
      2m 41s
    6. Calculating time intervals
      6m 55s
  7. 20m 9s
    1. Introducing the conditional IIf function
      2m 57s
    2. Creating an IIf function
      7m 31s
    3. Nesting IIf functions
      4m 57s
    4. Using the Switch function
      4m 44s
  8. 20m 41s
    1. Understanding the reporting tool
      2m 13s
    2. Building the form
      6m 57s
    3. Building the query
      5m 4s
    4. Building the report
      3m 30s
    5. Finalizing the reporting tool
      2m 57s
  9. 25m 37s
    1. Finding duplicate records
      2m 17s
    2. Identifying unmatched records
      2m 29s
    3. Creating crosstab results
      2m 57s
    4. Creating backups
      1m 29s
    5. Creating update queries
      3m 22s
    6. Making, deleting, and appending records
      5m 36s
    7. Uniting tables
      3m 16s
    8. Embedding SQL code in queries
      4m 11s
  10. 1m 0s
    1. Next Steps
      1m 0s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.