- By Andrew Couch
Adding calculated fields
Tables support the use of calculated fields, where the calculation can be based on a combination of other fields in the table. There are two advantages to using a calculated field rather than an expression in a query. The first advantage is that rather than possibly repeating the calculation in several places, the calculation is defined in one place in the design of the table.
The second benefit is that it might give you better performance, because the web app holds the results of the calculation with the data in the table. This means that it needs to recalculate the result only if any data in the dependent fields change, which it will do automatically. The storage type used for the calculated field will vary to suit the needs of the calculation, as defined in the Result Type field property for the calculation (which is a read-only property).
Figure 4-17 shows an example of a calculated expression in the Customers table.
FIGURE 4-17 Calculated field expression.
In this example, the calculation used is the following:
Coalesce([First Name]+" "+[Last Name],[Last Name],[First Name],[Email],[Company])
The Coalesce function returns the first non-null value in the list of expressions. Notice that the plus sign (+) symbol is used for string concatenation; this is because that is what SQL Server uses for concatenating strings. An Access desktop database can use either + or & for string concatenation. The advantage of using & is that if one part of the expression is NULL, it will still give a result. Here is an example:
"Andrew" + NULL = NULL, but "Andrew" & NULL = "Andrew"
To obtain a similar expression to the Access desktop database expression Trim$([First Name] & " " & [Last Name]), you would need to protect the string concatenation against a NULL—because you cannot use the & symbol but must use the + symbol.
You could use the following expression:
LTRIM(RTRIM(COALESCE([First Name], "") + " " + COALESCE([Last Name], "")))
Here you need to use the combination of left and right trim operations because the TRIM function has no equivalent function in SQL Server.