Selecting Data Using Queries in Microsoft Access 2013

Adding calculations with the expression builder

You can type calculations in queries directly into the query, both to create a column and as criteria filtering against an existing column. To avoid mistakes when referring to field names or to explore the myriad built-in functions, you can use the expression builder to provide assistance when creating more complex expressions.

Access provides a wide range of built-in functions to help you create expressions. Two particularly popular functions are the NZ function, which converts a NULL to a value such as 0 for a number field or to an empty string for a text field, and the IIF function, which allows you to conditionally perform calculations. You will also find that the Zoom box is particularly useful when you are working with complex expressions.

Create an expression with the expression builder

1. Click a blank column heading. Make sure that you have already saved your query; otherwise, you will not see the column names in the expression builder.

2. Click Builder (or right-click and select Build).

   

   Click to view larger image

3. In the Expression Builder dialog box, double-click the fields in the Expression Categories pane to add them to the expression.

4. Enter the functions between each field (such as *, +, -, and so on).

5. Click OK. The field expression will be read as Expr1:[Quantity]*[UnitPrice].

   

   CAUTION

   When you add a NULL value in an expression, Access makes the entire expression NULL. For example, in the following addition operation, 5 + NULL = NULL. This is where the NZ function is useful. In this case, it would convert the NULL to zero. So, rather than [Field1]+[Field2], we can use Nz([Field1]) + Nz([Field2]), which gives the result 5 + 0 = 5.

6. Click in the field, and press Shift+F2 (or right-click and select Zoom).

7. Edit the field title, and click OK.

   

   Click to view larger image