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

Adding criteria to queries

Figure 6-6 shows an example of criteria being added below some of the fields. I started with the previously created query, qryOrdersWithCriteria, and used the Show Table icon on the design ribbon to add the Products table to the query. Note that I cleared the Show box below [Order Date] because I already included this field using Orders.*. Also, note that the date range is displayed using the standard # symbols around the date, but the like criteria uses the % symbol as a wildcard and not the * (which is not supported). You can surround the text string by either single or double quotation marks. I chose double quotes in this example.

FIGURE 6-6 Sample query with criteria against numeric, date, and text fields.

If you looked at this saved query (which is saved as a view) in Azure SQL Database, you would notice that the criteria looks like this:

WHERE
    [Order Details].[UnitPrice] > 1.0 AND [Orders].[OrderDate]
    BETWEEN DATEFROMPARTS(1996, 1, 1) AND DATEFROMPARTS(1997, 1, 1)
    AND [Products].[ProductName] LIKE N'%rav%'

From this code, you can see how the web app displays the information in a format that is more familiar to people working with Access for the date criteria, but it actually saves the query in a true Azure SQL Database format.

TIP

For a wildcard search against text criteria, to save typing you can enter *rav*. This will then be displayed as Like “*rav*”. You can then change the * to %. Although the resulting view will use single quotes, the information you enter is preserved for display in either double or single quotes, depending on what you typed in. Getting used to using single quotes will help when switching between the web app and the Azure SQL Database. When searching on a Yes/No field, you cannot use True/False; you can use Yes/No or 1/0 or <>0/0. (In the Azure SQL Database, 0 is false and 1 is true.)

The query grid has multiple lines, each starting with the keyword OR. This allows you to construct several alternative sets of criteria for filtering the data. When you have multiple criteria against multiple columns on a single line of criteria, they act together to filter the data, such as criteria1 AND criteria2 AND criteria3. Each line of criteria acts as a set of alternatives. If we had criteria1 on the first line and criteria2 on the second line this would be criteria1 OR criteria2.