Creating Jet Expressions

Chapter 6 uses several functions to validate data entry for most fields in the HRActions table. Chapter 9 uses an expression to select the country and states to be included in a mailing-list query. These examples provide the foundation on which to build more complex expressions that define more precisely the validation rules and query criteria for real-world database applications.

To review examples using functions to restrict data-entry values, see "Validating Data Entry," p. 227.

For information on how to enter expressions in a query, see "Selecting Records by Criteria and Sorting the Display," p. 338.

The sections that follow provide a few examples of typical expressions for creating default values for fields, validating data entry, creating query criteria, and calculating field values. The examples demonstrate the similarity of syntax for expressions with different purposes. Part IV of this book, "Designing Forms and Reports," provides additional examples of expressions designed for use in forms and reports; Part VII, "Programming and Converting Access Applications," explains the use of expressions with Access VBA code.

Expressions for Creating Default Values

Expressions that create default field values can speed the entry of new records. Assigning values ordinarily requires you to use the assignment operator (=). When entering a default value in the properties pane for a table in design mode, however, you can enter a simple literal. An example is the Q default value assigned to the ActionType field of the HRActions table in Chapter 5, "Working with Jet Databases and Tables." In this case, Access (not Jet) infers the = assignment operator and the quotation marks surrounding the Q. You often can use shorthand techniques when typing expressions because Access infers the missing characters. If you type = "Q", you achieve the same result.

You can use complex expressions for default values if the result of the expression conforms to or can be converted by Access to the proper field data type. You can type = 1 as the default value for the ActionType field, for example, although 1 is a numeric value and ActionType has the Text data type. The Variant data type used for all Jet data operations permits this action.

T-SQL

The Upsizing Wizard converts Jet default values to SQL Server default values, if the expression for the default value contain functions that have T-SQL equivalents.

To review using the assignment operator to assign a default value, see "Setting Default Values of Fields," p. 187.

Expressions for Validating Data

The HRActions table uses several expressions to validate data entry. The validation rule for the EmployeeID field is > 0; the rule for the ApprovedBy field is > 0 Or Is Null. The validation rule for the EmployeeID field is equivalent to the following imaginary in-line VBA IIf function:

 IIf(DataEntry > 0, EmployeeID = DataEntry,     MsgBox("Please enter a valid employee ID number.")) 

Jet tests DataEntry in the validation rule expression. If the validation expression returns True, the value of DataEntry replaces the value in the current record's field. If the expression returns False, a message box displays the validation text that you added. MsgBox is a function used in VBA programming to display a message box onscreen. You can't type the imaginary validation rule just described as a property value; Jet infers the equivalent of the imaginary IIf expression after you add the Validation Rule and Validation Text property values with entries in the two text boxes for the EmployeeID field.

You might want to change the validation expression "H" Or "Q" Or "Y" Or "S" Or "R" Or "B" Or "C" Or "T", which you use to test the ActionType field, to a function. The In function provides a simpler expression that accomplishes the same objective:

 In("H", "Q", "Y", "S", "R", "B", "C", "T") 

Alternatively, you can use the following table-level validation expression:

 InStr("HQYSRBCT",[ActionType]) > 0 

Instr returns the position of the second argument's character(s) within the first argument's characters. If ActionType is Q, the preceding example returns 2. Both In and Instr expressions give the same result, but you can use InStr only for table-level validation because one of its arguments refers to a field name. Thus, the In function provides the better solution.

T-SQL

The Upsizing Wizard converts Jet default values to SQL Server default values, if the expression for the default value contain functions that have T-SQL equivalents.

Expressions for Query Criteria

When creating Chapter 9's qryStateMailList query to select records from the states of California, Oregon, and Washington, you type CA, OR, and WA on separate lines; Access adds the equal sign and double quotes around the literals for you. A better expression is In("CA", "OR", "WA"), entered on the same line as the ="USA" criterion for the Country field. This expression corrects the failure to test the Country field for a value equal to USA for the OR and WA entries.

If you're not sure how multiple criteria should look in the grid, see "Creating More Complex Criteria," p. 341.

You can use a wide range of other functions to select specific records to be returned to a query table. Table 10.9 shows some typical functions used as query criteria applicable to the Northwind Traders tables. (Table 10.9 uses 1997 as the year value, because 1997 has a full calendar year of data in the Northwind.mdb tables.)

Table 10.9. Typical Expressions Used as Query Criteria

Field	Expression	Records Returned
Customers Table
Country	Not "USA" And Not"Canada"	Firms other than those in the United States and Canada
Country	Not ("USA" Or "Canada")	Firms other than those in the United States and Canada; the parentheses apply the condition to both literals
CompanyName	Like "[N-S]*"	Firms with names beginning with N through S
CompanyName	Like S* Or Like V*	Firms with names beginning with S or V (Access adds quotation marks for you)
CompanyName	Like "*shop*"	Firms with shop, Shop, Shoppe, or SHOPPING in the firm name
PostalCode	>=90000	Firms with postal codes greater than or equal to 90000, including codes that begin with alphabetic characters
Orders Table
OrderDate	Year([OrderDate]) = 1997	Orders received in 1997
OrderDate	Like "*/*/1997"	Orders received in 1997; using wild cards simplifies expressions
OrderDate	Like "1/*/1997"	Orders received in the month of January 1997
OrderDate	Like "1/?/1997"	Orders received from the 1st to the 9th of January 1997
OrderDate	Year([OrderDate]) =1997 And DatePart("q", [OrderDate]) = 1	Orders received in the first quarter of 1997
OrderDate	Between #1/1/1997# And #3/31/1997#	Orders received in the first quarter of 1997
OrderDate	Year([OrderDate]) = 1997> And DatePart("ww", [OrderDate])= 10	Orders received in the 10th week of 1997
OrderDate	>= DateValue("1/15/1997")	Orders received on or after 1/15/1997
ShippedDate	Is Null	Orders not yet shipped
Order Subtotals Query
Subtotal	>= 5000	Orders with values greater than or equal to $5,000
Subtotal	Between 5000 And 10000	Orders with values greater than or equal to $5,000 and less than or equal to $10,000
Subtotal	< 1000	Orders less than $1,000

The wildcard characters used in Like expressions simplify the creation of criteria for selecting names and dates. As in the Windows Search dialog and DOS, the asterisk (*) substitutes for any legal number of characters, and the question mark (?) substitutes for a single character. When a wildcard character prefixes or appends a string, the matching process loses case sensitivity, if case-sensitivity is specified.

T-SQL

As mentioned earlier in the chapter, T-SQL substitutes % for * and _ (underscore) for ?. % and _ comply with ANSI SQL-92.

If you want to match a string without regard to case, use the following expression:

UCase(FieldName) = "FIELDNAME"

Entering a Query Criterion and Adding a Calculated Field

To experiment with query criteria expressions with tables from the Northwind.mdb database and add a calculated field value, follow these steps:

1. Click the Queries shortcut of the Database window and then double-click the Create Query in Design View shortcut to open the Query Design window and the Add Tables dialog.

2. Double-click the Customers, Orders, and Order Details tables in the Tables list of the Show Table dialog, and then click Close. The CustomerID fields of the Customers and Orders tables and the OrderID fields of the Orders and Order Details tables are joined; joins are indicated by a line between the fields of the two tables. (Chapter 11, "Creating Multitable and Crosstab Queries," covers joining multiple tables.)

   Note

   The Order Details table, which has Quantity, UnitPrice and Discount fields, is required to calculate the total amount of each order.

3. Add the CompanyName, PostalCode, and Country fields of the Customers table to the query. You can add fields by selecting them from the Field drop-down list in the Query Design grid, by clicking a field in the Customers field list above the grid and dragging the field to the desired Field cell in the grid, or by double-clicking a field in the Customers field list above the grid.

4. Add to the query the OrderID, ShippedDate, and Freight fields of the Orders table. Use the horizontal scroll bar slider under the Query Design grid to expose additional field columns as necessary. Place the cursor in the Sort row of the OrderID field, open the Sort list box, and select Ascending Sort. Add an Is Not Null criterion for the ShippedDate column to return only orders that have shipped.

5. Click the Totals button of the toolbar or choose View, Totals to add the Total row to the Query Design grid. The default value, Group By, is added to the Total cell for each field of your query. The Query Design view appears as shown in Figure 10.7.

   Figure 10.7. This multitable summary query has joins between the Customer and Orders, and the Orders and Order Details tables.

   

   Note

   The query requires Group By because the Order Details table has multiple rows for most orders. If you don't specify Totals, the query returns a row for each Order Details record.

6. Click the Run button on the toolbar to test the result of the interim query design, which returns 809 rows (see Figure 10.8).

   Figure 10.8. Design view of the interim query design of Figure 10.7 verifies that only one record appears for each order because of the addition of the Group By expression in the Total row.

   

7. Return to Design view, and scroll the grid so that the Freight column appears. Click the selection bar above the Field row to select the Freight column, and press the Insert key to add a new column.

8. Type Amount: CCur([UnitPrice]*[Quantity]*(1 [Discount])) in the new column's Field cell. This expression calculates the net amount of each line item in the Order Details table and formats the column as if the field data type were Currency. The next section discusses how to use expressions to create calculated fields.

9. Move the cursor to the Total row of the new column and press F4 to open the drop-down list. Select Sum from the list (see Figure 10.9). The Sum option totals the net amount for all the line items of each order in the Orders table. In the next chapter, you learn the details of how to create queries that group data.

   Figure 10.9. The calculated Amount column supplies the total net amount of the line items of the Order Details records for each Order.

   

   For other ways you can manipulate results from queries, see "Making Calculations on Multiple Records," p. 435.

   
   

   Tip

   The Total row for all the other columns of the query shows Group By. Make sure that you mark the Show check box so that your new query column appears when you run the query.

   Don't make an entry in the Table row of your new query column; if you do, you receive an error message when you run the query.

10. Click the Run or Datasheet View button on the toolbar to run your new query. Your query appears as shown in Figure 10.10. The Amount column contains the total amount of each order, which is net of any discounts.

    Figure 10.10. Datasheet view confirms that the Amount column totals the net amount of each line item for an order.

    

Using the Expression Builder to Add Query Criteria

After creating and testing your query, you can apply criteria to limit the number of records that the query returns. You can use Access's Expression Builder to simplify the process of adding record-selection criteria to your query. To test some of the expressions listed in Table 10.9, follow these steps:

1. Click the Design View button on the toolbar to change to Query Design mode.

2. Place the cursor in the Criteria row of the field for which you want to establish a record-selection criterion.

3. Click the Build button on the toolbar to display the Expression Builder's window. Alternatively, you can right-click the Criteria row and then choose Build from the pop-up menu.

4. In the Expression text box at the top of Expression Builder's window, type one of the expressions from Table 10.9. Figure 10.11 shows the sample expression Like "*shop*" that applies to the Criteria row of the Company Name column. You can use the Like button under the expression text box as a shortcut for entering Like.

   Figure 10.11. You can use the Expression Builder to add simple or complex expressions as WHERE clause criteria.

   

5. Click OK to return to the Query Design grid. The Expression Builder places the expression that you built in the field where the cursor is located (see Figure 10.12).

   Figure 10.12. The expression you create in the Expression Builder applies to the field you selected when opening the Builder.

   

6. Click the Run button on the toolbar to test the expression. The query result for the example in Figure 10.12 appears as shown in Figure 10.13.

   Figure 10.13. The Like "*shop*" expression displays records only for customers whose names contain "shop", "Shop", or "SHOP".

   

7. Return to Query Design mode; then select and delete the expression by pressing the Delete key.

8. Repeat steps 2 through 7 for each expression that you want to test. When you test expressions using Date/Time functions, sort the OrderDate field in ascending order. Similarly, sort on the Amount field when queries are based on amount criteria. You can alter the expressions and try combinations with the implied And condition by entering criteria for other fields in the same row. Access warns you with an error message if you make a mistake in an expression's syntax.

9. After you finish experimenting, save your query with a descriptive name, such as qryInvoiceAmount.

The preceding query and its underlying tables are included in Chaptr10.mdb sample file, located in the \Seua10\Chaptr10 folder of the accompanying CD-ROM.

Jet SQL

The Jet SQL statement for the qryOrderAmount query is

[View full width]
 SELECT Customers.CompanyName, Customers.PostalCode,    Customers.Country, Orders.OrderID, Orders.ShippedDate,    Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount]))) AS Amount,    Orders.Freight FROM (Customers    INNER JOIN Orders       ON Customers.CustomerID = Orders.CustomerID)    INNER JOIN [Order Details]       ON Orders.OrderID = [Order Details].OrderID GROUP BY Customers.CompanyName, Customers.PostalCode, Customers. Country,    Orders.OrderID, Orders.OrderDate, Orders.Freight    HAVING (((Customers.CompanyName) Like "*shop*") AND       ((Orders.ShippedDate) Is Not Null)) ORDER BY Orders.OrderID; 

The Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount]))) AS Amount expression combines the Sum aggregate operation you specified (refer to Figure 10.9) with the expression you typed to define the calculated Amount column.

Each INNER JOIN...ON clause defines the joins between two tables; JOIN clauses are one of the subjects of the next chapter.

You might think that the GROUP BY clause includes more fields than required and only the Orders.OrderID field is required for grouping. One of the Total aggregate functions must appear in each column of a grouped query.

The HAVING clause for grouped rows is the equivalent of the WHERE clause for individual rows.

If the Jet or SQL Server query designer throws an error when you attempt to run a query with aggregate functions, check the "Troubleshooting" section near the end of this chapter.

The SQL Server Version of a Query

If you've installed MSDE2002 to support ADP, you can run the Upsizing Wizard on the Chaptr10.mdb database to create the SQL Server version of the tables and qryOrderAmount query. Chapter 22, "Upsizing Jet Applications to Access Data Projects," provides detailed examples of the upsizing process.

For instructions on how to install MSDE2000 from the Office 2003 distribution CD-ROM, see "SQL Server 2000 Desktop Engine Setup," p. 47.

The Chaptr10.mdb database that the accompanying CD-ROM installs in your \Program Files\Seua11\Chaptr10 folder contains the Customers, Orders, and Order Details tables and the qryInvoiceAmount query you created in the preceding section. To upsize the Chaptr10.mdb database to an SQL Server Chaptr10SQL database and a Chaptr10CS.adp project, do this:

1. Verify that the Chaptr10.mdb file doesn't have the Read-Only attribute applied, and then open it in Access.

2. Choose Tools, Database Utilities, Upsizing Wizard to start the upsizing process.

3. Accept the default Create New Database option in the first Wizard dialog, and click Next.

4. In the second dialog, accept the default Use Trusted Connection to use your Administrator logon account for MSDE. Click Next.

5. In the third dialog, click the >> button to add all three tables to the SQL Server database, and click Next.

6. In the fourth and fifth dialogs, accept the defaults, and click Next.

7. In the sixth and last dialog, accept the default Open the New ADP File option, and click Finish to upsize the database. After a minute or so, depending on the speed of your computer, the Chaptr10CS.adp project opens and displays an eight-page Upsizing Wizard report.

8. Close the report to return to the Database window, click the Queries shortcut, and double-click the query, which the Wizard upsizes to an SQL Server 2000 (user defined) function. The Datasheet view of the query is identical to that of the Jet version of the query (refer to Figure 10.10).

9. Click the Design button to open the da Vinci Filter Design window. The three tables and the joins between them appear in the upper pane. Field definitions, including the calculated Amount field definition, appear in the lower pane.

10. Click the SQL toolbar button to display the filter's T-SQL statement. Adjust the position of the table windows and the depths of the three panes as shown in Figure 10.14.

    Figure 10.14. The da Vinci Design view of a filter, one of SQL Server's three choices for generating query result sets, has a three-pane window.

    

T-SQL

The The T-SQL statement for the qryInvoiceAmount function (without a WHERE criterion on the Customers column) is

[View full width]
 SELECT TOP 100 PERCENT dbo.Customers.CompanyName, dbo.Customers. PostalCode,    dbo.Customers.Country, dbo.Orders.OrderID, dbo.Orders.  ShippedDate,    SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo. [Order Details].Quantity) *       (1 - dbo.[Order Details].Discount))) AS Amount, dbo.Orders. Freight FROM dbo.Customers    INNER JOIN dbo.Orders       ON dbo.Customers.CustomerID = dbo.Orders.CustomerID    INNER JOIN dbo.[Order Details]       ON dbo.Orders.OrderID = dbo.[Order Details].OrderID GROUP BY dbo.Customers.CompanyName, dbo.Customers.PostalCode,    dbo.Customers.Country, dbo.Orders.OrderID, dbo.Orders. OrderDate,    dbo.Orders.Freight HAVING (dbo.Orders.ShippedDate IS NOT NULL) ORDER BY dbo.Orders.OrderID 

The SQL statement is similar to that of the Jet query, but substitutes CONVERT(money ) for Jet's CCur function. The TOP 100 PERCENT prefix is required to permit an ORDER BY clause in a view or function. The dbo. prefix identifies the default database owner.

Expressions for Calculating Query Field Values

The three preceding sections demonstrate that you can use expressions to create new, calculated fields in query tables. Calculated fields display data computed based on the values of other fields in the same row of the query table. Table 10.10 shows some representative expressions that you can use to create calculated query fields. Notice that Jet field names must be enclosed with square brackets when typed in the Query Design window.

Table 10.10. Typical Expressions to Create Calculated Query Fields

Column Name	Expression	Values Calculated
TotalAmount	[Amount] + [Freight]	Sum of the OrderAmount and Freight fields
FreightPercent	100 * [Freight]/[Amount]	Freight charges as a percentage of the order amount
FreightPct	Format([Freight]/[Amount], "Percent")	Freight charges as a percentage of the order amount, but with formatting applied
SalesTax	Format([Amount] * 0.08, "$#,###.00")	Sales tax of 8 percent of the amount of the order added with a display that's similar to the Currency data type

Note

T-SQL doesn't support the VBA Format function, and the Upsizing Wizard won't generate views or functions from Jet queries that use this function.

To create a query containing calculated fields, follow these steps:

1. In Query Design view, move to the first blank column of the qryInvoiceAmount query. Type the column name shown in Table 10.10, followed by a colon and then the expression:

    Total Invoice: [Amount]+[Freight] 

   Note

   If you don't type the field name and colon, Access provides the default Expr1 as the calculated field name.

2. Place the cursor in the Total cell of the calculated field and select Expression from the drop-down list. If you don't select Expression, your query opens a Parameters dialog or returns an error message when you attempt to execute it.

3. Move to the next empty column, type the following expression, and add the Expression aggregate (see Figure 10.15):

    Freight Pct: Format([Freight]/[Amount],"Percent") 

   Figure 10.15. Type one of the expressions of Table 10.10 to add an additional calculated column. The example shown here calculates Total Invoice and Freight Pct column values from another calculated column, Amount, and a table field, Freight.

   

   

   To avoid this error, see "Query Expressions Fail to Execute" in the "Troubleshooting" section at the end of the chapter.

   
   

4. Remove the Like "*shop*" criterion from the CompanyName column.

5. Run the query. The result set for the query with the added calculated fields appears as shown in Figure 10.16.

   Figure 10.16. Datasheet view displays the query result set of the design shown in Figure 10.15.

   

6. Repeat steps 3 through 5 for the remaining examples in Table 10.10.

You use the Format function with your expression as its first argument to display the calculated values in a more readable form. When you add the percent symbol (%) to a format expression or specify "Percent" as the format, the value of the expression argument multiplies by 100 and the percent symbol preceded by a space appends to the displayed value.

If you run into a "Can't evaluate expression" or "Wrong data type" error, check the "Troubleshooting" section near the end of this chapter.

Tip

Use the Format function with custom percent formatting if you want fewer or more decimal places. For example, if you only want one digit to the right of the decimal separator, substitute FreightPct: Format([Freight]/[Amount],"#0.0%") for the standard formatting in the preceding example. Adding the % symbol to the format string automatically multiplies the value argument by 100.

Avoid the use of the Format or Format... functions in tables you plan to upsize to SQL Server. T-SQL doesn't support these two functions.