MS-Access / Getting Started

---

Creating Calculated Fields

Access 2010 introduces a new data type called Calculated. SharePoint lists already have a Calculated data type, so if you create a Calculated field in a web database, the field and data will move to the server if you publish the database. This new data type can be used in client databases as well as web databases so the steps we show you here to create Calculated fields in web tables can be applied to client tables.

The Calculated data type allows you to create a calculated result using an expression. The expression you use can include data from one or more fields in the same table, but you cannot reference fields in other tables. If you have a number field, for example, that holds quantity information for products purchased and a currency field that holds the price of a product, you can create a calculated field that multiplies the quantity and price fields and stores it with a result type of currency. Access recalculates the value of the calculated field any time the dependent fields are changed. If the calculation for a specific row evaluates to an error, Access stores the error for the Calculated field. For example, if your expression result divides a number by zero, Access displays the error #Div/0 in the Calculated field.

In client databases, you can reference all data types except Multi-Value and OLE Object fields in Calculated field expressions. In web databases, you cannot reference Multi-Value, OLE Object, Memo, Lookup, and AutoNumber fields for Calculated field expressions to maintain parity with SharePoint Calculated columns.

Calculated field expressions can reference functions and use operators that are supported in SharePoint Calculated columns. You cannot use volatile functions, such as Date() and Now(), in Calculated fields, because the stored value of the field would always be incorrect. You cannot use functions that look up outside the current record of the table, such as DSum, DCount, and DLookup, because Access cannot track when field dependencies change in those scenarios. You also cannot create an index or a relationship on a Calculated field. You can, however, use a Calculated field as a display column for a lookup field. You'll learn how to create relationships with lookup fields later in this tutorial.

In Access 2010, you can generally use Calculated fields anywhere you can use other data types in your database. You can sort, filter on, and group by Calculated fields in client and web queries, forms, and reports. If your Calculated field evaluates to an error and you are filtering or grouping on that field, your query or report will fail to run. Access sorts Calculated field errors as Null values in table datasheets.

In the Vendors table you have been building in the Restaurant Database, let's add a Calculated field that concatenates, or combines, the vendor contact's first and last name. Open your Vendors table in Datasheet view, if it isn't already, and tab into the ContactLastName field. (Access creates the new field to the right of the field that currently has focus in Datasheet view so we are starting you off in the last name field.) Next, click the More Fields button in the Add & Delete group on the Fields contextual tab, highlight the Calculated Field option near the bottom of the drop-down list, and then click Text on the shortcut menu.

Access opens the Expression Builder dialog box. In the upper part of the dialog box is a blank text box in which you can build an expression. You can type the expression yourself, but it's sometimes more accurate to find field names, operators, and function names in the three panes in the lower part of the dialog box.

The expression you need to build, which we'll walk you through in detail in just a moment, will look like this:

[ContactFirstName] & " " & [ContactLastName]

You can use the Expression Builder to help you correctly construct the expression we need for our new Calculated field. The Expression Builder exists in previous of Access, but in Access 2010, Microsoft enhanced the dialog box with some new capabilities. The first major enhancement to the Expression Builder in Access 2010 is a concept called progressive disclosure. In previous versions of Access, the Expression Builder showed all available functions, constants, and operators for expressions regardless of context. In Access 2010, the Expression Builder shows you only the elements applicable to the current context.

When you are working in the Expression Builder, you can press Ctrl+Space to show the IntelliSense list of available items based on where your insertion point is located. If no characters precede your insertion point, Access displays all available items. Press the Esc key to dismiss the IntelliSense list at any time.

If the font size in the upper part of the Expression Builder seems too small for you to read, you can adjust the size of the font by holding down the Ctrl key and then scrolling with your mouse wheel. When you scroll up, Access increases the font size, and when you scroll down, Access decreases the font size. Access returns the font size to the default size whenever you open the Expression Builder dialog box.

Caution Don't use the plus sign (+) for string concatenation in web databases. Traditional Access client databases support the use of both the ampersand (&) and the plus sign (+) for concatenation of strings. However, the server only supports the use of the ampersand (&) for string concatenation. If you attempt to use the plus sign (+) in an expression with string values, you'll receive an error in any controls that use the expression after you publish your database to a SharePoint server and work with your database objects in a web browser.