Expressions with Text
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, then you obviously can't use addition, subtraction, and other mathematical operations. 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]
Note: Access has two types of text values: those you draw from other fields, and those you enter directly (or hard-code). When you hard-code a piece of text (such as the comma and space in the previous example), you need to wrap it in quotation marks so Access knows where it starts and stops.
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]
