In the Real WorldThe Algebra of Access Expressions

In the Real World The Algebra of Access Expressions

A junior high school algebra class provides most students their first introduction to abstract mathematics. Expressions (algebraic formulas) are crucial to the majority of decision-support queries you design, as well as presentation of calculated data in form and report text boxes and other text-based controls. The colon following the column name of a calculated expression is the equivalent of an equal sign; in mathematical terms, Amount: CCur([UnitPrice]*[Quantity]*(1 [Discount])) is the equivalent of curAmount = CCur(sngUnitPrice*intQuantity*(1 sngDiscount)) in VBA.

Similarly, functions that convert data types and format query columns also are important to forms and reports. The classic definition of a function is this: If when X is given, Y is determined, then Y is a function of X. For example, in Price: Format([UnitPrice], "#,##0.00"), the value of the UnitPrice field (X) uniquely determines the value of the calculated Price (Y) column. The fact that queries can have calculated and specially formatted columns is one of the reasons this book uses the term column with queries and field for tables.

In most cases, it's a good design practice to base forms and reports on queries with precalculated and preformatted columns, rather than calculating and formatting values for individual text boxes. It's quicker and easier to check your expressions in the query result set, and you don't need to add expressions or formatting (or both) to every text box, subform, and other control that displays the data.

The only drawback of this approach is that calculating and formatting columns of queries with a large number of rows slows performance, but usually only slightly. For instance, formatting the nine-digit StudentID column of the 45,000-record StudentTransactions table of the Oakmont.mdb database with the ID: Left([StudentID], 3) & "-" & Mid([StudentID], 4, 3) & "-" & Right([StudentID], 3) expression causes an imperceptible effect on query execution speed. Bear in mind, however, that adding calculated columns to queries against networked databases increases the amount of data sent "over the wire." Calculated columns slow networked query execution by the proportion of characters added per row, as does applying formatting that increases the number of characters per column. The alternative is to perform calculations and add formatting by customized controls on Access forms.

Expressions and, to a lesser extent, functions play a major role in query criteria. When you type a criterion such as CA in the query design grid, Access converts the criterion to a valid Jet SQL expression, in this case WHERE FieldName = "CA". In this example, the equal sign is the identity operator. Another use for the identity operator is in creating joins using SQL WHERE clauses, as in WHERE Table2.PrimaryKey = Table1.ForeignKey. SQL Server required the use of WHERE syntax to define joins prior to adopting the SQL-92 JOIN syntax in SQL Server 6+.

You can perform logical operations on query result sets with the IIf (inline If) function, whose arguments can contain other functions. For instance, the equivalent of the Province: Nz([Region], "None") expression is Province: IIf(IsNull([Region]),"None",[Region]). You must use the IIf function in Data Access Pages (DAP), the subject of Chapter 24, "Designing and Deploying Data Access Pages," because the Nz function isn't marked "safe for scripting."