Home > Sample chapters

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

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.

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.