Sort Records

Sorting the datasheet is a quick way to find a particular record or to view records with the same entry in a particular field.

Text, number and date/time data can be sorted in ascending or descending order.

• Ascending (A to Z or 1 to 10 or January to December).
• Descending (Z to A or 10 to 1 or December to January).

Sorts are not case sensitive. For example, words beginning with "a" and "A" will be mixed together in the sort.

To sort data by a single field

• Select the column (field) by which you want to sort the data
• On the Table Datasheet toolbar, click Sort Ascending or Sort Descending

OR

• From the Records menu, select Sort then Ascending or Descending

To sort data by more than one field

If you select more than one field in datasheet view, Access will sort by the fields in the order in which they are displayed in the view. Data will be sorted by the first selected column, then any duplicate values from that sort will be sub-sorted by the values in the second column, and so on.

Tip: To change the order of columns, click the column header and drag it left or right.

Apply and Remove a Filter

Filters allow you to limit the number of records that are visible in a datasheet to a manageable sub-set of the records. You can apply filters to tables in datasheet view and to query recordsets.

A filter is defined by entering criteria for one or more fields in the query. If a record matches the criteria, it will be included in the query results. You can enter criteria by selecting values from fields or by entering criteria using a form.

To use Filter by Selection

The simplest way of filtering the display of records in a table is use Filter by Selection. This displays only records that have the same value in that field.

• Display the table in datasheet view
• Select the value in a particular field that you have chosen as the filter value

Tip: If you select part of a field, then the filter will be based on that partial selection. For example, if you pick 'F' in the third position of a field and filter this by example, then the results will be exactly that - records that have an 'F' in the third position of that field.

For example, you might want to display items for which you have no stock level, or examine the customers where your contact is the owner.

• On the Table Datasheet toolbar, click Filter by Selection

OR

• From the Records menu, select Filter then select Filter by Selection

The datasheet is refreshed to display only records that pass the selected filter criteria. You should always get at least one record - the one that you selected for the filter.

You can show a subset of the filtered records by adding to the Filter by Selection.

• If you select another field from a filter-limited list, and select Filter by Selection once more, the records displayed will be those that match both criteria

You can add successive Filters by Selection, until the only the records you require are displayed.

To re-display all records

Once you have set a filter, the Apply/Remove Filter button lets you toggle between the filter results and displaying all records.

• On the Table Datasheet toolbar, click Remove Filter to toggle between filtered records and all records

Tip: This button can also be used to turn off the filter when you have finished using it.

OR

• From the Records menu, select Remove Filter/Sort

Use Filter by Form

While Filter by Selection allows you to filter on exact values, it is often necessary to filter the information in your datasheet by a range of values. Access provides an option to Filter by Form.

Filter by Form re-displays the datasheet with only the column headings and one blank row. You create the filter by entering criteria into one or more fields in this row.

Note that if you fill in more than one column with a filter expression, then the expressions must all be true for the record to be displayed (sometimes known as "AND-ing").
Access also allows you to "OR" several fields together, using an OR tab at the bottom of the filter window. When you select the OR tab, another blank row is displayed, allowing you to enter another set of criteria. Records matching criteria on either tab will be selected.

To apply a Filter by Form

• On the Table Datasheet toolbar, click Filter by Form

OR

• From the Records menu, select Filter then Filter by Form

The table window is redisplayed in Filter by Form mode.



Tip: As the name implies, you can also use Filter by Form with forms!

Filter by Form resembles an empty datasheet record, into which you can enter criteria.

• Type the filter selection criteria into the appropriate field(s)

Tip: Each field has a drop-down list of all the values available in the datasheet, which you can use to select criteria.

• If you want to edit existing filter criteria, select the appropriate field and press F2
• To add OR criteria for multiple fields, select the OR tab at the bottom-left corner of the window

A new empty Filter by Criteria row is displayed.

• Enter your OR filter criteria
• Add additional OR criteria as appropriate
• To display filtered records, on the Table Datasheet toolbar, click Apply/Remove Filter

OR

• From the Filter menu, select Apply Filter/Sort
• To display all records again, click Remove Filter

OR

• From the Records menu, select Remove Filter/Sort

The filter results remain selected until you close the datasheet. Use the Apply/Remove Filter button to toggle between them.

• To clear the results and start a new filter, on the Table Datasheet toolbar, click Filter by Form again

To use operators to specify criteria

When you create criteria using Filter by Form, you can use operators to specify how the data is to match the criteria value. For example, you could filter all records where the Order Value is Less Than 100. The table below shows a few examples of operators and criteria.



You can see a full list of criteria by asking the Office Assistant "I need help specifying criteria".