Using the New Expression Builder to Build If-Then Statements in Access 2010 Applications

  • Article

Office Visual How To

Summary:  Use the improved Expression Builder to create a nested IIf expression in Access 2010.

Applies to: Access 2010 | Access Services | Office 2010

Published:  September 2010

Provided by:  Ken Getz, MCW Technologies, LLC

Overview

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.

Code It

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:

  • If the stock on hand is greater than the amount ordered, "In stock".

  • If the stock on hand is one less than the amount ordered, "1 item back ordered".

  • If the stock on hand is more than one less than the amount ordered, "x items back ordered", where x represents the difference between the items ordered and the stock on hand.

Create the Sample Database

To create the sample database, use the following procedure.

To create the sample database

  1. Start Access 2010, then click File and then click New.

  2. In the list of available templates, under Office.com Templates, select Samples.

  3. From the list of sample databases, select Northwind 2007.

  4. Select an appropriate path, and then click the Download button to download and install a clean copy of the sample database. If prompted, click the Enable Content button in the Message Bar to enable the VBA code in the sample database.

  5. Accept the default logon ID in the Login Dialog form, and then click Login.

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

  1. Select the Create tab, and then click Query Design.

  2. In the Show Table dialog box, from the Tables tab, add the Orders table and then the Order Details table.

  3. Select the Queries tab, and add the Inventory query.

  4. In the query designer, select the Product ID field in the Order Details table, and drag it to the Product ID field in the Inventory query, creating a join between the table and the query.

  5. When the designer has created the join line, double-click it to edit its properties.

  6. In the Join Properties dialog box, select option 2 (include ALL records from 'Order Details' and only those records from 'Inventory' where the joined fields are equal), then click OK to close the dialog box.

  7. In the designer, from the Orders table, drag the Order Date field to the design grid.

  8. From the Order Details table, drag the Product ID and Quantity fields to the design grid.

  9. From the Inventory query, drag the Qty on Hand field to the design grid.

  10. In the next empty column in the designer grid, in the Field row, add the following expression: Backordered: [Quantity]-[Qty on Hand]

  11. Click the Run button and verify that you see output as shown in Figure 1.

    Note

    The newly created Backordered column will contain values that are either positive (indicating the number of backordered items), 0 (indicating that you have the exact quantity required to fill the order), or negative (indicating that you have excess quantity available).

  12. Click the View button to return to Design view.

  13. Select File and then Save. Save the query as Query1.

    Note

    The columns in the query will not be available in the Expression Builder unless you first save the query.

Figure 1. Sample order query

Sample order 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.

Figure 2. Build option menu item

Build option menu item

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.

Figure 3. Expression Builder dialog box

Expression Builder dialog box

Be aware of the following important features of the Expression Builder:

  • The Expression Elements list on the left contains a list of all the types of elements that you can add to an expression. These include fields from the current query, functions, database items from the current database, constants, operators, and common expressions.

  • Expand the Functions node to display a list of available functions. The list includes built-in VBA functions, functions that are defined in the current applications, and functions that are defined in available web services (see Figure 4).

    Figure 4. List of available functions query


    List of available functions query

  • Expand the node that corresponds to the current database, and then expand the Tables node (or the node for any other database object). You will see a list of available objects, and you can select the object to display a list of available values for the object, as shown in Figure 5.

    Figure 5. List of table fields query


    List of table fields query

  • The Constants, Operators, and Common Expressions nodes provide built-in VBA constants, VBA operators, and some common expressions for your use in 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 (:)).

DisplayText:

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).

Figure 6. IntelliSense tips in Expression Builder

IntelliSense tips in Expression Builder

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.

Figure 7. IIf function in Expression Builder

IIf function in Expression Builder

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.

Note

Double-clicking the function also adds an additional Expr element. You must delete the extraneous term before continuing. Therefore, delete the item highlighted in Figure 8.

Figure 8. IIf function call

IIf function call

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.

"In stock."

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.

Figure 9. Calculated field in the query

Calculated field in the query

 

Read It

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/1ee515c3-94be-4d50-bf78-d3486ee5ece4]

Length: 11:22

Explore It

About the Author
Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).