Lesson 8
Home Up Lesson 1 Lesson 2 Lesson 3 Lesson 4 Lesson 5 Lesson 6 Lesson 7 Lesson 8

ASCII table

Assignment 8

Filtering Records

A set of criteria applied to records in order to show a subset of the records or to sort the records. Microsoft Access has four kinds of filters: Filter By Selection, Filter By Form, Advanced Filter/Sort, and Filter For Input.

Comparison of Filtering Methods

What do you want the filter to do? Filter By Selection Filter By Form and Filter For Input Advanced Filter/Sort
Search for records that meet multiple criteria. Yes (but you must specify the criteria one at a time) Yes (and you can specify the criteria all at once) Yes (and you can specify the criteria all at once)
Search for records that meet one criteria or another criteria. No Yes Yes
Allow you to enter expressions as criteria. No Yes Yes
Sort records in ascending or descending order. No (however, after applying the filter, you can sort all filtered records by clicking Sort Ascending or Sort Descending on the toolbar) No (however, after applying the filter, you can sort all filtered records by clicking Sort Ascending or Sort Descending on the toolbar) Yes (you can sort some fields in ascending order and other fields in descending order)

Filter by Selection

  1. In a field on a form, sub form, or datasheet, find one instance of the value you want records to contain in order to be included in the filter's results.
  2. Select the value, and then click Filter By Selection  on the toolbar. How you select the value determines what records the filter returns.
  3. Repeat step 2 until you have the set of records you want.

Notes

When you save a table or form, Microsoft Access saves the filter. You can reapply the filter when you need it, the next time you open the table or form.
When you save a query, Microsoft Access saves the filter, but it does not add the filter criteria to the query design grid. You can reapply the filter after you run the query, the next time you open it.
You can also filter for records that do not have a certain value. After selecting a value, right-click it, and then click Filter Excluding Selection.

Filter by Form and Filter for Input

In a form, datasheet, or either filter window, click Apply Filter

 on the toolbar.

Note

If you create a filter in a form or datasheet that already contained a filter from a previous working session, the new filter replaces the previous one.

Advanced Filter/Sort

  1. Open a table, query, or form in Datasheet view or open a form in Form view.
  2. Click Filter By Form  on the toolbar to switch to the Filter By Form window.
  3. Click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records.
  4. Enter your criteria by selecting the value you're searching for from the list in the field (if the list includes field values), or by typing the value into the field.
To find records in which a check box, toggle button, or option button is or is not selected, click the check box or button until it's the way you want. To return it to a neutral position so that it won't be used as criteria for filtering records, continue clicking the check box or button until it's grayed.
To find records in which a particular field is empty or not empty, type Is Null or Is Not Null into the field. (You can select these options from the list in fields with a Memo, OLE Object, or Hyperlink data type, and in calculated fields in queries.)
To find records using a criteria expression, type the expression into the appropriate field or enter one using the Expression Builder.

If you specify values in more than one field, the filter returns records only if they contain the same values you specified in each of those fields.

  1. To specify alternative values that records can have to be included in the filters results, click the Or tab at the bottom of the window, and enter more criteria.

The filter returns records if they have all the values specified on the Look For tab or all the values specified on the first Or tab or all the values specified on the second Or tab, and so on.

  1. Click Apply Filter  on the toolbar.

Notes

When you save a table or form, Microsoft Access saves the filter. You can reapply the filter when you need it, the next time you open the table or form.
When you save a query, Microsoft Access saves the filter, but it does not add the filter criteria to the query design grid. You can reapply the filter after you run the query, the next time you open it.

Examples of expressions that use text values as criteria

Field Expression Description
ShipCity "London" Displays orders shipped to London.
ShipCity "London" Or "Hedge End" Uses the Or operator to display orders shipped to London or Hedge End
ShippedDate Between #1/5/95# And #1/10/95# Uses the Between...And operator to display orders shipped no earlier than 5-Jan-95 and no later than 10-Jan-95
ShippedDate #2/2/95# Displays orders shipped on 2-Feb-95.
ShipCountry In("Canada", "UK") Uses the In operator to display orders shipped to Canada or the UK.
ShipCountry Not "USA" Uses the Not operator to display orders shipped to countries other than the USA.
ShipName Like "S*" Orders shipped to customers whose name starts with the letter S.
CompanyName >="N" Displays orders shipped to companies whose name starts with the letters N through Z.
OrderID Right([OrderID], 2)="99" Uses the Right function to display orders with OrderID values ending in 99.
CompanyName Len([CompanyName])>Val(30) Uses the Len and Val functions to display orders for companies whose name is more than 30 characters long.

Examples of expressions that calculate or manipulate dates, and then use the result as criteria

Field Expression Description
RequiredDate Between Date( ) And DateAdd("m", 3, Date( )) Uses the Between...And operator and the DateAdd and Date functions to display orders required between today's date and three months from today's date.
OrderDate < Date( )- 30 Uses the Date function to display orders more than 30 days old.
OrderDate Year([OrderDate])=1996 Uses the Year function to display orders with order dates in 1996.
OrderDate DatePart("q", [OrderDate])=4 Uses the DatePart function to display orders for the fourth calendar quarter.
OrderDate DateSerial(Year ([OrderDate]), Month([OrderDate])+1, 1 )-1 Uses the DateSerial, Year, and Month functions to display orders for the last day of each month.
OrderDate Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now()) Uses the Year and Month functions and the And operator to display orders for the current year and month.