MS-Access / Getting Started

---

NULL Predicate

Determines whether the expression evaluates to Null or not Null. This predicate evaluates only to True or False and will not evaluate to undefined.

Syntax

<expression> IS [NOT] NULL

Example

To determine whether the contact work phone number column contains the Null value, enter the following:

tblContacts.WorkPhone IS NULL

See also Expression, SELECT Statement, Subquery, and WHERE Clause in this tutorial.

ORDER BY Clause

Specifies the sequence of rows to be returned by a SELECT statement or a subquery.

Syntax

ORDER BY {column-name | column-number [ASC | DESC]},...

Notes You use column names or relative output column numbers to specify the columns on whose values the rows returned are ordered. (If you use relative output column numbers, the first output column is 1.) You can specify multiple columns in the ORDER BY clause. When you specify multiple columns, the list is ordered primarily by the first column. If rows exist for which the values of that column are equal, they are ordered by the next column in the ORDER BY list, and so on. When multiple rows contain the matching values in all the columns in the ORDER BY clause, the database can return the matching rows in any order. You can specify ascending (ASC) or descending (DESC) order for each column. If you do not specify ASC or DESC, ASC is assumed. Using an ORDER BY clause in a SELECT statement is the only means of defining the sequence of the returned rows.

When you include the DISTINCT keyword or use the UNION query operator in the SELECT statement, the ORDER BY clause can include only columns specified in the SELECT clause. Otherwise, you can include any column in the logical table returned by the FROM clause.

To use ORDER BY in a view, function, or stored procedure in SQL Server, you must also include the TOP keyword in the SELECT clause. To fetch and sort all rows, specify TOP 100 PERCENT. Note, however, that a view, function, or stored procedure returns the result ordered only when you directly execute the query from code. When Access runs a query in SQL Server that is identified as the record source of a form or report or the row source of a combo box or list box, it sends a SELECT * FROM queryname command to the server. The server returns the rows sorted only when you specify the ORDER BY clause again in the record source or row source as part of a SELECT statement on the query.

Examples

To calculate the total for all invoices and list the result for each customer and invoice in descending sequence by order total, enter the following (qxmplOrderTotalSorted):

SELECT TOP 100 PERCENT tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate, Sum(tblContactProducts.SoldPrice) AS InvoiceTotal
 FROM (tblCompanies
  INNER JOIN tblInvoices
  ON tblCompanies.CompanyID = tblInvoices.CompanyID)
  INNER JOIN tblContactProducts
  ON tblInvoices.InvoiceID = tblContactProducts.InvoiceID
 GROUP BY tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate
ORDER BY Sum(tblContactProducts.SoldPrice) DESC;

Note: The TOP keyword is optional in a desktop database (.accdb). In SQL Server, you can also specify the calculated column alias name in the ORDER BY clause: ORDER BY InvoiceTotal DESC. In a desktop database, you must repeat the calculation expression as shown in the example.

In a desktop database (.accdb), to create a mailing list for all companies and all contacts, sorted in ascending order by postal code, enter the following (qxmplSortedMailingList):

SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
  tblCompanies.StateOrProvince, tblCompanies.PostalCode
 FROM tblCompanies
UNION
SELECT [FirstName] & " " & ([MiddleInit]+". ") & [LastName] AS Contact,
  tblContacts.HomeAddress, tblContacts.HomeCity,
  tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
 FROM tblContacts
ORDER BY 5;

Note: If you decide to use column names in the ORDER BY clause of a UNION query, the database derives the column names from the names returned by the first query. In this example, you could change the ORDER BY clause to read ORDER BY PostalCode.

To create the same mailing list in a view or in-line function in an SQL Server database, enter the following:

SELECT TOP 100 PERCENT CompanyName, Address, City,
  StateOrProvince, PostalCode
FROM
(SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
  tblCompanies.StateOrProvince, tblCompanies.PostalCode
 FROM tblCompanies
UNION
SELECT tblContacts.FirstName + ' ' +
  IsNull(tblContacts.MiddleInit + '. ', '') +
  tblContacts.LastName AS Contact,
  tblContacts.HomeAddress, tblContacts.HomeCity,
  tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
 FROM tblContacts) AS U
ORDER BY 5;

Notice that you must UNION the rows first and then select and sort them all.