Filtering a Datasheet in Access 2003

Filtering an Access 2003 datasheet is a way to focus on specific records, rather than all the records in a table. You can filter out records that aren't relevant to what you're trying to do at the moment.

When you filter data, you use criteria to tell Access what you want to see. A criterion is a test that the data passes in order to display after the filter applies. For example, you may ask Access to show you the records with an order date of 5/1/03. A more advanced criterion is orders with a date on or after 5/1/03.

You can use five types of filters in a table: Filter by Selection, Filter by Form, and Advanced Filter/Sort. Table 1 shows you how to use each filter.

Table 1: Types of Datasheet Filters

Type of Filter	When You Should Use It
Filter by Selection	You have a record with a certain value in a field, and you want to find all the other records that have the same value in that field.
Filter by Form	You have more than one criterion; for instance, you want to find orders placed before 6/1/03 that were paid for by credit card.
Filter for Input	You want to type the value or values that you're looking for in a particular field, or you want to use an expression as your criterion.
Filter Excluding Selection	You can find a record with a certain value in a field, and you want to exclude all the records that have the same value in that field.
Advanced Filter/Sort	You want to do more than the other filters allow, such as sorting and applying criteria to multiple fields. Advanced Filter/Sort creates a query using only one table.

Filtering basics

If you want to get a handle on the whole filtering concept, start out by taking a look at the parts of a datasheet that relate to filters. Figure 1 shows a datasheet with the filter buttons and indicators marked. This datasheet has a filter applied — you can tell because of the Filtered datasheet indicators at the bottom of the datasheet. Also, the Apply/Remove Filter button is highlighted — if it's clicked again, the filter is removed and all the records in the datasheet are displayed.

>

Figure 1: A filtered datasheet.

You can apply a filter to any datasheet — that includes a table, of course, but also subdatasheets and datasheets generated by queries. You can enter and edit data in a filtered datasheet. Just be aware that the filter is not applied to any new records until you reapply the filter (by choosing Records --> Apply Filter/Sort).

You may filter by using the Filter by Selection or Filter by Form buttons, or you can use the menu. Display the Records menu to see the filtering options, including those that don't have buttons (there are some on the Records menu, and some in the Filter sub-menu).

• To reapply the last filter you applied, choose Records --> Apply Filter/Sort.

• To remove a filter, click the Apply Filter button or choose Records --> Remove Filter/Sort.

A filter runs a simple query on one table — a good way to start analyzing your data. Filtering can help you warm up to creating more complex queries. If you're confused about queries, creating a filter can help you figure out how to write criteria for a query. When you create the filter, choose Records --> Filter --> Advanced Filter/Sort to see it in the query grid. Look at the Criteria row to see what the criteria look like. To close the query grid, click the Close button on the toolbar.

Filters appear in the Filter property of the Properties sheet. You can filter a table by entering an expression there, but almost no one does that because the filter stays applied, and some records may be filtered out the moment you open the table.

Filtering by selection

Filtering by selection is the simplest kind of filter — it finds records with matching values in one field. To filter by selection, follow these steps:

1. Find a record with the value or text you want to match and then place your cursor in that cell to match the whole value.

• To find all products with the price of 29.99, place the cursor in a Price cell with the value 29.99.

• To match the beginning of the value, select the first character and as many thereafter as you want to match. To find all entries in the field that start with La, highlight the La in Lawn Flamingo before filtering.

• To match part of the value, select the characters in the middle of a value that you want to match. Select 99 to find all values that contain 99, such as 499.

2. Click the Filter by Selection button on the toolbar

Access filters the datasheet to display only records that have the same value in that field.

To see the entire table, click the Remove Filter button (which is the flip side of the Apply Filter button; the same button on the toolbar toggles between the Apply Filter button and the Remove Filter button).

Filtering by exclusion

Filtering by exclusion is very similar to filtering by selection, except that rather than seeing only records that match your criteria, all the records that match are excluded from the datasheet.

To filter by exclusion, select a value to exclude in the same way that you select a value to match when filtering by selection:

• Place the cursor anywhere in the cell to exclude values that match the whole value. For instance, place the cursor in a cell with the value ME to exclude all addresses in the state of Maine.

• Select the beginning of the value and as many characters thereafter as you want to match to exclude all records with matching beginning values.

• Select the characters in the middle of a value to exclude all records containing the selection anywhere in the field.

To filter by exclusion after you select the values you want to exclude, choose Records --> Filter --> Filter Excluding Selection. (This type of filter isn't on the toolbar.)

>
dummies

Source:http://www.dummies.com/how-to/content/filtering-a-datasheet-in-access-2003.html