Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

640

Calculated Fields and the

Expression Builder

Access has an Expression Builder that helps you create any expression, such as a complex calcu-

lated field for a query. In the next example, you create a calculated field named DueDate that dis-

plays a date 45 days in the future, based on an invoice date in tblSales. You can use this date

for a letter you plan to send to all buyers that have outstanding invoices:

1. Create a new query using tblSales from the Chapter18.accdb database and add
   InvoiceNumber, SaleDate, and InvoiceDate to the QBE pane.


2. Click an empty Field cell in the QBE pane, and then click the Builder button in the
   Query Setup group on the ribbon to open the Expression Builder (see Figure 18.3).

In the next several steps, you use the DateAdd function in a calculated field. The
DateAdd function adds a specified number of days, weeks, months, quarters, or years to
another date. In this example, it is adding 45 days to the invoice date value.

3. Go to the Expression Elements area of the Expression Builder dialog box and expand the
   Functions tree.

FIGURE 18.3

The Expression Builder dialog box

4. Select Built-in Functions in the Expression Elements area, then click on Date/Time in the
   Expression Categories area.


5. Select the DateAdd function (by double-clicking it) from the Expression Values area on
   the right.

Access places the DateAdd function in the top window, with information about the
three parameters needed by DateAdd.