Using the New Expression Builder to Build If-Then Statements in Access 2010 Applications
Summary: Use the improved Expression Builder to create a nested IIf expression in Access 2010.
Last modified: September 12, 2012
Applies to: Access 2010 | Access Services | Office 2010
Published: September 2010
Provided by: Ken Getz, MCW Technologies, LLC
In Microsoft Access 2010, the Expression Builder gains several new features that make it easier to use, and more pertinent to developers. This Visual How To shows how to create a nested IIf function call when you design a query by using the new Expression Builder and its features.
To use the Expression Builder, follow the steps outlined in the following sections. For this example, you will use the sample Northwind 2007 database, and you will create a new query that is based on the Order and Order Details tables, together with the Inventory query, adding a new field that contains the following text:
Create the Sample Database
To create the sample database, use the following procedure.
To create the sample database
Now, you should have downloaded and installed a fresh copy of the Northwind 2007 sample database and it should be loaded in Access 2010.
Create the Query
To create the query, use the following procedure.
To create the query
Examine the Expression Builder
So far, you have created the query and added a simple expression, without the help of the Expression Builder. To create the new, more complex expression that describes the backordered status, follow these steps. In the Design grid, in the next empty column, right-click and select Build from the context menu, as shown in Figure 2.
Selecting the Build option displays the Expression Builder dialog box, as shown in Figure 3. Notice that the Expression Builder displays, among other items, all the fields in the current query.
Be aware of the following important features of the Expression Builder:
Create the Expression
To create an If…Then statement for use in a query, use the built-in VBA function, IIf. To get started, create a name for the calculated field. In the Expression Builder, type the following (including a space after the colon (:)).
To add the IIf function, you have several choices. You can start to type or you can select from a list. Try typing the IIf function name first and you will see that the Expression Builder supplies IntelliSense as you type (see Figure 6).
Using IntelliSense requires you to know the function name. You can also select the function from the list of available functions in the Expression Builder. To try this, delete the part of the IIf function that you have typed, and then expand the Functions node and select Built-In functions. In the Expression Categories list, select Program Flow, and notice the IIf function in the Expression Values list (see Figure 7). Notice that you can also select the <All> option in the Expression Categories list, and then type the name of the function to find it in the Expression Values list.
Double-click the IIf function in the Expression Values list, and the Expression Builder adds the function to its expression, as shown in Figure 8.
Your objective is to replace each of the placeholders in the Expression Builder with appropriate expressions. Start by selecting the expression term. In the Expression Elements list select Query1 and in the Expression Categories list, double-click Backordered. To complete the first expression, modify the text so it matches the following.
DisplayText: IIf( [Backordered] = 1, «truepart», «falsepart»)
Select the «truepart» element, and replace it with the following text.
"1 item back ordered."
Select the «falsepart» element, and again locate and double-click the IIf function in the list of available functions. In the new IIf function call, select the «expression» element, and locate and double-click the Backordered field in Query1. Modify the expression so that it resembles the following.
DisplayText: IIf( [Backordered] = 1, "1 item back ordered.", IIf( [Backordered] > 1, «truepart», «falsepart») )
Click the «truepart» element to select it, and replace it with the following expression (you can select the Backordered field from the list of fields in Query1 again).
[Backordered] & " items back ordered."
Select the «falsepart» element and replace it with the following expression.
When you are finished, the complete expression appears similar to the following.
DisplayText: IIf([Backordered]=1,"1 item back ordered.", IIf([Backordered]>1,[Backordered] & " items back ordered.", "In stock."))
Click OK, adding the expression to the query. Click Run, and the query should now include the calculated field as shown in Figure 9.
Use the Expression Builder in many places within Access 2010, including properties in the Properties window when you design forms, reports, and tables. Before you create an expression, right-click the value, and see if the Build option is available. In addition, when you see the Build button (…), you can use the Expression Builder. Notice the IntelliSense tips when you use the Expression Builder─these tips help you enter expressions more quickly and accurately. You can always press TAB or ENTER to accept the item selected in the IntelliSense tip, or you can use the UP ARROW and DOWN ARROW keys to select another option.
About the Author