Microsoft Office Tutorials and References
In Depth Information
3. Click the (Select All) check box to deselect all check boxes, click the East Grand
Rapids check box, and then click the Grand Rapids check box.
The two check boxes indicate that the AutoFilter will include only East Grand
Rapids and Grand Rapids City field values.
4. Click the OK button. Access displays the four records for customers in East Grand
Rapids and Grand Rapids with small contract amounts or with winter signing dates.
See Figure 5-9.
Using an AutoFilter to filter records in the query recordset
toggles f ilter
off and on
Grand Rapids and East Grand
Rapids customers selected
You click the Toggle Filter button in the Sort & Filter group on the Home tab to
remove the current filter and display all records in the query. If you click the
Toggle Filter button a second time, you reapply the filter.
5. In the Sort & Filter group on the Home tab, click the Toggle Filter button. Access
removes the filter, and all 31 records appear in the recordset.
6. Click the Toggle Filter button. Access applies the City filter, displaying the four
records for customers in East Grand Rapids and Grand Rapids.
Taylor knows how to use the AutoFilter feature and has the information she needs.
You can close the query without saving your query design changes.
7. Close the query without saving the query design changes.
Sarah wants to view all fields from the tblCustomer table, along with the customer
name. The customer name is either the company name for nonresidential customers or
the last and first names for residential customers.
Assigning a Conditional Value to a
Records for residential customers have nonnull FirstName and LastName field values and
null Company field values in the tblCustomer table, while records for all other customers
have nonnull values for all three fields. Sarah wants to view records from the tblCustomer
table in order by the Company field value, if it’s nonnull, and at the same time in order
by the LastName and then FirstName field values, if the Company field value is null. To
produce the information for Sarah, you need to create a query that includes all fields
from the tblCustomer table and then add a calculated field that will display the customer
name—either the Company field value or the LastName and FirstName field values,
separated by a comma and a space.
To combine the LastName and FirstName fields, you’ll use the expression LastName &
“, “ & FirstName .The & (ampersand) operator is a concatenation operator that joins text
expressions. If the LastName field value is Maloney and the FirstName field value is
Sharon, for example, the result of the expression is Maloney, Sharon .