Extend Microsoft Access Applications to the Cloud: Creating a Blank Web App and Using Templates

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).

TIP

Because the calculation results are saved, you are not allowed to use expressions in a calculation that could vary over time. For example, you can calculate the number of days between two date fields in the table but not the number of days between a date field and today’s date (because that calculation varies depending on the current date). In SQL Server terminology, you are allowed to use only deterministic expressions, not nondeterministic expressions.

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.

TIP

If you examined this calculation in Microsoft Azure SQL Database, you would see the following expression:

(ltrim(rtrim((coalesce([First Name],N'')+N' ')+coalesce([Last Name],N'')))),

Here you can see that the double quotes have been switched to single quotes, and the use of the N symbol next to the string expressions indicates that they should be read as Unicode strings.