Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries

Adding calculations to queries

If you are familiar with creating calculations using a desktop database, you will find that the calculations in a web app are significantly different. The reason for this is that the queries are stored in Azure SQL Database as views and must conform to the SQL language syntax used in the Azure SQL Database, the built-in Azure SQL Database functions, and additional Access support functions in the Azure SQL Database—not the syntax and functions used in a desktop database.

TIP

The query calculation syntax does not allow the use of the & symbol when concatenating strings. In a desktop database, developers often use the & when concatenating strings because it protects against a NULL value causing the resulting string to be NULL. In the web app, you must use the plus sign (+) when concatenating strings and use a built-in function to avoid problems with NULL values, such as COALESCE([FieldName], ''). The COALESCE function takes multiple arguments and returns the first non-null value.

There are 52 built-in functions you can use in web app queries, together with 25 constants. Rather than simply listing all of these (which you can see when using the builder in the query design tool), I will provide a few guidelines about using popular functions to get you started constructing expressions.

The Azure SQL Database has more strict rules than the desktop Access environment when it comes to adding together different data types and performing arithmetic operations—for example, on dates.

For example, if you try to construct the following expression, you will get an error:

NameAndID: [Products].[ProductID]+' '+[ProductName]

In this case, you need to convert ProductID to a string, and you do this by using the CAST function to change one data type to another data type. (Note that the data types used here—for example, ShortText—are not true Azure SQL Database data types; they are the data type names used when creating fields in a web app.)

NameAndID: Cast([Products].[ProductID],ShortText)+' '+[ProductName]

If you want to remove spaces from the beginning and end of a string, you need to use the following syntax because the Trim function is not supported:

NoSpaceField: LTRIM(RTRIM([FieldName]))

To replace a substring inside a larger string, use the REPLACE function. To extract part of the text in a string, use the SUBSTR function. There are also STUFF and REPLICATE string functions.

Another useful feature in a query is if you divide, for example, an integer by 10, you will find that in the view this is replaced by division by 10.0. This is because in the Azure SQL Database 3/10 = 0 and 3/10.0 = 0.3. However, you need to be a bit careful with this. Figure 6-7 shows the result of dividing two whole-number columns and an alternative calculation that forces the divisor to be a floating point number. Note that this happens only because we are using whole-number columns for the FirstNumber and SecondNumber fields.

FIGURE 6-7 Dividing 3/10 is zero when using integer arithmetic.

The two expressions are as follows:

FirstDividedBySecond: [FirstNumber]/[SecondNumber]

FirstDividedBySecondFloat: [FirstNumber]/Cast([SecondNumber],Float)

Calculations can also be constructed using the Builder icon (shown in Figure 6-8) on the design ribbon, or by right-clicking in a column header or criteria and selecting Build.

FIGURE 6-8 Expression Builder.

TIP

You might think that the integer division issue is a bug or problem in the web app, but this is not the case. This is simply a contrast between how an Access desktop database performs a calculation and how the Azure SQL Database performs calculations. You will also find that when you add a Number field to a table, the default Number Subtype is Fixed-Point Number (6 decimal places), and because this is not an integer, the division issue does not arise.