Microsoft Office Tutorials and References
In Depth Information
Adding calculations with the expression builder
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).
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].
2
1
4
5
(continued on next page)
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 Field1 ]) + Field2 ]), Nz([
which gives the result 5 + 0 = 5.
3
Search JabSto ::




Custom Search