Part II: Programming Microsoft Access
484
The query in Figure 13.6 is an excellent example of a typical business requirement. In this case, these
results could be used by the sales department to follow up with cold calls or marketing materials.WHERE
Use the WHERE clause of the SQL statement only when you want to restrict returned records based
on a condition. Unlike SELECT and FROM, the WHERE clause is optional.The SQL statement shown in Figure 13.1 specifies the following WHERE clause:WHERE (tblCustomers.State=“NY”) Or (tblCustomers.State=“CT”)The WHERE condition can be any valid Boolean (True or False) expression. It can be an evalua-
tion on a single field, as in the previous example, or a complex expression based on several criteria.Note
If you use the WHERE condition, it must follow the FROM clause of the SQL statement.
You’ll see many, many examples of WHERE clauses throughout this book, so there is no need to
show a lot of examples at this time.ORDER BY
The ORDER BY clause is how the returned data is sorted. A query sorts the returned data by the
field(s) you specified in the ORDER BY clause, in ascending or descending order. Using the exam-
ple in Figure 13.1, the query was sorted by two of the fields in the SELECT clause:ORDER BY tblCustomers.Company, tblSalesLineItems.Description;The fields in the ORDER BY clause appear in the same left-to-right order that they appear in the
QBE grid in the query’s design. The data is sorted by the fields in the ORDER BY clause in left-to-
right order. In this case, the returned data is first sorted by company, then by description. Be sure
to specify a sort order that makes sense for your data.Because a SQL statement can be as long as 64,000 characters, a way is needed to tell the database
language that you’ve finished creating the statement. By default, a semicolon (;) indicates the end of a
SQL statement, but the semicolon is not required by Access.Access is very forgiving about the ending semicolon. If you forget to place one at the end of a SQL state-
ment, Access assumes that it should be there and runs the SQL statement. On the other hand, if you
accidentally place a semicolon inside a SQL statement, Access reports an error and tries to tell you
where it occurred.Specifying the end of a SQL statement