To create a calculated field, you need to supply two details: a name for the field, and an expression that tells Access what calculation it must perform. Calculated fields are defined using this two-part form:

CalculatedFieldName: Expression

For example, here’s how you can define the PriceWithTax calculated field:

PriceWithTax: [Price] * 1.10

Essentially, this expression tells Access to take the value from the Price field, and then multiply it by 1.10 (which is equivalent to raising the price by 10 percent). Access repeats this calculation for each record in the query results. For this expression to work, the Price field must exist in the table that the query is using. However, you don’t need to show the Price field separately in the query results.

You can also refer to the Price field using its full name, which is made up of the table name, followed by a period, followed by the field name, as shown here:

PriceWithTax: [Products].[Price] * 1.10

This syntax is sometimes necessary if your query involves more than one table (using a query join, as described on Joining Tables in a Query), and the same field appears in both tables. In this situation, you must use the full name to avoid ambiguity. (If you don’t, Access gives you an error message when you try to run the query.)

To add the PriceWithTax calculated field to a query, you need to use Design view. First, find the column where you want to insert your field. (Usually, you’ll just tack it onto the end in the first blank column, although you can drag the other fields around to make space.) Next, type the full definition for the field into the Field box (see Figure 7-1).

Figure 7-1. This query shows two fields straight from the database (ID and Name), and adds the calculated PriceWithTax field. The ordinary Price field, which Access uses to calculate PriceWithTax, isn’t shown at all.

Now you’re ready to run the query. When you do, the calculated information appears alongside your other columns (Figure 7-2). If you don’t like the fact that your calculated information appears in a slightly messier format—with more decimal places and no currency symbol—you can fix it by using the rounding (Nested Functions) and formatting (Formatting Numbers) features discussed later in this chapter.

Figure 7-2. The query results now show a PriceWithTax field, with the result of the 10 percent markup. The neat part is that this calculated information is now available instantaneously, even though it isn’t stored in the database. Try to beat that with a pocket calculator.

Calculated fields do have one limitation—since the information isn’t stored in your table, you can’t edit it. If you want to make a price change, you’ll need to edit the underlying Price field—trying to change PriceWithTax would leave Access thoroughly confused.

Before going any further, it’s worth reviewing the rules of calculated fields. Here are some pointers:

OrderDate: DateCustomerPlacedPurchaseOrder

Many calculated fields rely entirely on ordinary high school math. Table 7-1 gives a quick overview of your basic options for combining numbers.

You’re free to use as many fields and operators as you need to create your expression. Consider a Products table with a UnitsInStock field that records the number of units in your warehouse. To determine the value of what you have on hand for a given product, you can write this expression that uses two fields:

ValueInStock: [UnitsInStock] * [Price]

ExtendedDeadline: [DueDate] + 14

ShippingLag: [ShipDate] - [OrderDate]

LateDays: [DateSubmitted] - #03/20/2013#

TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]

TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]

Although calculated fields usually deal with numeric information, they don’t always. You have genuinely useful ways to manipulate text as well.

If you have text information, you obviously can’t use mathematical operations like addition and subtraction. However, you can join text together. You can, for instance, link several fields of address information together and show them all in one field, conserving space (and possibly making it easier to export the information to another program).

To join text, you use the ampersand (&) operator. For example, here’s how to create a FullName field that draws information from the FirstName and LastName fields:

FullName: [FirstName] & [LastName]

This expression looks reasonable enough, but it’s actually got a flaw. Since you haven’t added any spaces, the first and last name end up crammed together, like this: BenJenks. A better approach is to join together three pieces of text: the first name, a space, and the last name. Here’s the revised expression:

FullName: [FirstName] & " " & [LastName]

This produces values like Ben Jenks. You can also swap the order and add a comma, if you prefer to have the last name first (like Jenks, Ben) for better sorting:

FullName: [LastName] & ", " & [FirstName]

You can even use the ampersand to tack text alongside numeric values. If you want the slightly useless text “The price is” to appear before each price value, use this calculated field:

Price: "The price is: " & [Price]

Whether you’re using the simplest or the most complicated function, the syntax—the rules for using a function in an expression—is the same. To use a function, simply enter the function name, followed by parentheses. Then, inside the parentheses, put all the information the function needs in order to perform its calculations (if any).

For a good example, consider the handy Round() function, which takes a fractional number and then tidies up any unwanted decimal places. Round() is a good way to clean up displayed values in a calculated field. You’ll see why Round() is useful if you create an expression like this, which discounts prices by 5 percent:

SalePrice: [Price] * 0.95

Run a price like $43.97 through this expression, and you wind up with 41.7715 on the other side—which doesn’t look that great on a sales tag. The Round() function comes in handy here. Just feed it the unrounded number and the number of decimal places you want to keep:

SalePrice: Round([Price] * 0.95, 2)

Technically, the Round() function requires two pieces of information, or arguments. The first is the number you want to round (in this case, it’s the result of the calculation Price * 0.95), and the second is the number of digits that you want to retain to the right of the decimal place (2). The result: the calculation rounded to two decimal places, or 41.77.

Speed: Abs([DistanceTraveled] / [TimeTaken])

Speed: Round(Abs([DistanceTraveled] / [TimeTaken]), 2)

Speed: Abs(Round([DistanceTraveled] / [TimeTaken], 2))

Functions are a great innovation, but Access just might have too much of a good thing. Access provides a catalog of dozens of different functions tailored for different tasks, some of which are intended for specialized mathematical or statistical operations.

To quickly find the functions you want, Access provides a tool called the Expression Builder. To launch the Expression Builder, follow these steps:

Most Access experts find that the Expression Builder is too clunky to be worth the trouble. But even though the Expression Builder may not be the most effective way to write an expression, it’s a great way to learn about new and mysterious functions, thanks to its built-in function reference. If you find a function that sounds promising but you need more information, select it in the list and then click Help. Access opens a web browser and shows you a brief reference summary that explains the purpose of the function and the arguments you need to supply, as shown in Figure 7-6.

Figure 7-6. The reference for the Round() function spells out what it does, and explains the two parameters. One parameter—the number of decimal places—is wrapped in square brackets, which means it’s an optional value. Leave it out, and Access rounds to the nearest whole number. You’ll also notice a table of contents on the left that lets you browse to any other Access function and read its description.

Format() is one interesting mathematical function that transforms numbers into text. Format() is interesting because the text it creates can be formatted in several different ways, which lets you control exactly how your numbers are presented.

To understand the difference, think back to the expression you used earlier for discounting product prices:

SalePrice: [Price] * 0.95

Even if the Price field has the Currency data type, the calculated values in the Sale-Price field appear as ordinary numbers (without the currency sign, thousands separator, and so on). So you see 43.2 when you might prefer $43.20.

You can remedy this problem by using the Format() function to apply a currency format:

SalePrice: Format([Price] / 0.95, "Currency")

Now the calculated values include the currency sign. Even better, since currencies are displayed with just two decimal places, you no longer need to clean up fractional values with the Round() function.

The trick to using the Format() function is knowing what text to supply for the second argument in order to get the result you want. Table 7-2 spells out your options.

The mathematical functions in Access don’t get much respect, because people don’t need them terribly often. You’ve already seen Round() and Format()—the most useful of the bunch—but from time to time Access mavens still turn to a few others in calculated fields. They’re listed in Table 7-3.

So far, all the functions you’ve seen have worked with numeric data. However, there’s still a lot you can do with text. Overall, you can manipulate text in three ways:

Table 7-4 shows the most common functions people use with text.

Using these functions, you can create a calculated field that shows a portion of a long text value or that changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here’s an example of a filter condition that selects records that start with Choco:

Left([ProductName], 5) = "Choco"

Figure 7-7 shows how you enter this filter condition.

The Len() function is a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len() function isn’t too useful in a simple calculated expression, because you’ll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information):

Len(Description) < 15

Figure 7-7. You can use the Left(), Right(), and Mid() functions in much the same way as you use the Like operator (page 151) to help you match bits and pieces of long text values.

InStr("Hi There", "he")

FirstWordProduct: Left([ProductName],
 InStr([ProductName], " ") - 1)

You’ve already seen how you can use simple addition and subtraction with dates (Date Fields). However, you can accomplish a whole lot more with some of Access’s date functions.

Without a doubt, everyone’s favorite date functions are Now() and Date(), which you first saw in Chapter 4 (Validating Dates). These functions grab the current date and time, or just the current date. You can use these functions to create queries that work with the current year’s worth of orders.

Here’s a filter condition that uses Date() to select projects that are past due:

=<Date()

Add this to the Criteria box for the DueDate field, and you’ll see only those records that have a DueDate that falls on or before today.

<(Date()+1)

=<Now()

Date logic becomes even more powerful when paired with the DatePart() function, which extracts part of the information in a date. DatePart() can determine the month number or year, letting you ignore other details (like the day number and the time). Using DatePart() and Date(), you can easily write a filter condition like this one, which selects all the orders placed in the current month:

DatePart("m", [DatePlaced])=DatePart("m", Date())
 And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date())

This rather lengthy expression is actually a combination of two conditions joined by the And keyword. The first condition compares the month of the current date with that of the date stored in the DatePlaced field:

DatePart("m", [DatePlaced])=DatePart("m", Date())

This expression establishes that they’re the same calendar month, but you also need to make sure it’s the same year:

DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date())

The trick to using DatePart() (and several other date functions) is understanding the concept of date components. As you can see, using the text m with the DatePart() functions gets the month number, and using the text yyyy extracts a four-digit year. Table 7-5 shows all your options.

You use the date components with several date functions, including DatePart(), DateAdd(), and DateDiff(). For example, the DateAdd() function lets you perform more complex date arithmetic that adds entire weeks, months, or years to a date. (By comparison, just using the plus sign, as you learned to do on Date Fields, always adds a number of days to a date.) Table 7-6 describes DateAdd() and several other useful date-related functions.

Databases have two types of fields: required and optional. Ordinarily, fields are optional (as discussed on Data Integrity Basics), which means a sloppy person can leave a lot of blank values. These blank values are called nulls, and you need to handle them carefully.

If you want to write a filter condition that catches null values, simply type this text into the Criteria box:

Is Null

This condition matches any fields that are left blank. Use this on the CustomerID field in the Orders table to find any orders that aren’t linked to a customer. Or ignore unlinked records by reversing the condition, like so:

Is Not Null

Sometimes, you don’t want to specifically search for (or ignore) null values. Instead, you want to swap those values with something more meaningful to the task at hand. Fortunately, there’s an oddly named tool for just this task: the Nz() function.

The Nz() function takes two arguments. The first is a value (usually a query field) that may contain a null value. The second parameter is the value that you want to show in the query results if Access finds a null value. Here’s an example that uses Nz() to convert null values in the Quantity field to 0:

Nz([Quantity], 0)

Converting to 0 is actually the standard behavior of Nz(), so you can leave off the second parameter if that’s what you want:

Nz([Quantity])

At this point, you may not be terribly impressed at the prospect of changing blank values in your datasheet into zeroes. But this function is a lifesaver if you need to create calculated fields that work with values that could be null. Consider this innocent-seeming example:

OrderItemCost: [Quantity] * [Price]

This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse, if the OrderItemCost enters into another calculation or a subtotal, that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells.

To correct this problem, use the Nz() function to clean up any potential nulls in optional fields:

OrderItemCost: Nz([Quantity]) * Nz([Price])

Finally, you can use Nz() to supply a different value altogether. In a text field, you may choose to enter something more descriptive. Here’s an example that displays the text “[Not Entered]” next to any record that doesn’t include name information:

Name: Nz([LastName], "[Not Entered]")