Create a filter (MDB)

Microsoft Office Access 2003

previous page next page
  1. In a field on a form, subform, datasheet, or subdatasheet, find one instance of the value you want records to contain in order to be included in the filter's results.
  2. Select all or part of a value in a field by doing one of the following:

    Note  How you select the value determines what records the filter returns.

    ShowFind records in which the entire contents of that field match the selection

    • Select the entire contents of a field or place the insertion point in a field without selecting anything.

      For example, select the value "Berlin" in the City field to return all records with Berlin as the city.

    ShowFind records in which the value in that field starts with the same characters you selected.

    • Select part of a value starting with the first character in a field.

      For example, Select only "Fran" in the CompanyName field with the value "France restauration" to return all records that have a company name starting with "Fran," such as "Franchi S.p.A." and "Frankenversand".

    ShowFind records in which all or any part of the value in that field contains the same characters you selected.

    • Select part of a value starting after the first character in a field.

      Select the letters "Del " in the CompanyName field with the value "Old World Delicatessen" to return all records that have "del" anywhere in the CompanyName field, such as "Ernst Handel", "Galería del gastrónomo", and "Que Delícia".

  3. Click Filter By Selection Button image on the toolbar.

  4. If you want to refine your search, you will need to reset the filter to show all of the records and then repeat steps 2 and 3. To reset the filter, click Remove Filter Button image on the toolbar.

Note  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.

ShowFilter records by entering values in a blank view of your form or datasheet (Filter By Form)

  1. Open a form in Form view, or a table, query, or form in Datasheet view.

    To filter records in a subdatasheet, display the subdatasheet by clicking its expand indicator.

  2. Click Filter By Form Button image on the toolbar to switch to the Filter By Form window.

    You can specify criteria for the form, subform, main datasheet, or any subdatasheet that's displayed. Each subform or subdatasheet has its own Look For and Or tabs.

  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.

    ShowFind 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.

    ShowFind 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.)

    ShowFind 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.

  5. To specify alternative values that records can have to be included in the filter's results, click the Or tab for the form, subform, datasheet, or subdatasheet you're filtering, 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.

  6. Click Apply Filter Button image on the toolbar.

ShowFilter records by entering criteria in place in a form or datasheet (Filter For Input)

  1. Open a form in Form view, or a table, query, or form in Datasheet view.

    To filter records in a subdatasheet, first click the expand indicator to display that subdatasheet.

  2. Do one of the following:

    ShowSpecify the exact field value you want the filtered records to contain

    • Right-click in the field in the form, subform, datasheet, or subdatasheet you're filtering, and then in the Filter For box on the shortcut menu, type the value.

      For example, to find all the records that have "London" in the City field, right-click in the City field, and then type London in the Filter For box.

    ShowSpecify more complex criteria

    • Type the complete expression using the appropriate combination of identifiers, operators, wildcard characters, and values to produce the result you want.

    For example:

    • To find all the records where the Contact Title field includes the word "Marketing" anywhere in the title, right-click in the Contact Title field, and then type *Marketing* in the Filter For box.
    • To display only the records for orders that were shipped more than 15 days after the specified required date, type [ShippedDate]-[RequiredDate]>15. (In this example, you don't have to right-click a specific field to enter the expression. However, in a datasheet with subdatasheets, first click in the sheet you want to filter.)
  3. Do one of the following:
    • To apply the filter and close the shortcut menu, press ENTER.
    • To apply the filter and keep the shortcut menu displayed (so that you can specify additional criteria for the field), press TAB. Enter new criteria, and then press TAB again. Repeat until you have just the records you want.

ShowFilter records by using the Advanced Filter/Sort window

  1. Open a form in Form view, or a table, query, or form in Datasheet view.
  2. Click in the form, subform, datasheet, or subdatasheet, you want to filter.
  3. On the Records menu, point to Filter, and then click Advanced Filter/Sort.
  4. Add to the design grid the fields you'll need to specify the values or other criteria the filter will use to find records.
  5. To specify a sort order, click in the Sort cell for a field, click the arrow, and select a sort order.

    Microsoft Access first sorts the leftmost field in the design grid, then it sorts the next field to the right, and so on.

  6. In the Criteria cell for the fields you have included, enter the value you're looking for or enter an expression.
  7. Apply the filter by clicking Apply Filter Button image on the toolbar.

ShowCreate a filter in a data access page

ShowCreate a filter to limit records in a Page before they are retrieved from the database

ShowCreate a filter on an ungrouped data access page

  1. Open the data access page in Design view.
  2. If the fields you want to display aren't in the section already, add them to the page.
  3. Add the field you want to use for filtering as a text box, bound span, drop-down list box, or list box.
  4. Right-click the filter field control and click Group Filter Control.
  5. If necessary, customize the record navigation control. For example, on a page that finds one record at a time, not all of the buttons are required, so you might want to show only the New, Delete, Save, Undo, and Help buttons on the record navigation toolbar.

Notes

  • When you create a group filter control, the GroupFilterField and GroupFilterControl properties are automatically set for the group level. If you convert a text box control or a bound span control to a group filter control, it will change to a drop-down list box control and the following control properties will be set: ListRowSource, ListBoundField, and ListDisplayField.

  • If you add a group filter control to a group level that already has one, the previous control will change to a simple drop-down list box control.

  • To convert a group filter control to a regular control, right-click the control and click Group Filter Control. The control will change to a regular drop-down list box.

ShowCreate a filter on a grouped data access page

  1. Open the data access page in Design view.
  2. If the fields you want to display aren't in the section already, add them to the page.
  3. Add the field you want to use for filtering as a text box, bound span, drop-down list box, or list box.
  4. Select the filter field control and do one of the following:
    • To group only on that field, click Promote Button image on the toolbar.
    • To group on the whole table or query that contains that field, click Group by Table Button image on the toolbar.

    Note  If you used the Page Wizard to create the page, the Group by Table option will not be available.

    Microsoft Access adds a group header with an expand control and a record navigation section with a record navigation control. If you grouped on one field, Access moves the control on which you grouped to the group header. If you grouped on a table or query, Access moves all the controls— including controls bound to fields that are in that table or query and controls bound to Lookup fields— to the group header.

  5. Right-click the filter field control, and then click Group Filter Control.

The following steps are optional. They remove features that were added by default for a grouped page but aren't necessary when using the group filter control. Do one or more of the following:

  • Select the expand control in the header that contains the filter control, and then press DELETE.
  • Select the record navigation section for the group level that contains the filter control, and then press DELETE. Access deletes the section and the record navigation control that it contains.
  • Customize the record navigation control for the lower group level. For example, hide the buttons that add and delete records.

Notes

  • If the group header also contains a control that calculates a total or other aggregate, when you choose a group from the group filter, the calculated control displays the correct total or other aggregate for that group of records.
  • You can have multiple group levels, each with one group filter. For example, you can create a group filter for a Country field, and then in the next lower group level, create a group filter for the City field, and so on.

  • When you create a group filter control, the GroupFilterField and GroupFilterControl properties are automatically set for the group level. If you convert a text box control or a bound span control to a group filter control, it will change to a drop-down list box control and the following control properties will be set: ListRowSource, ListDisplayField, and ListBoundField.

  • If you add a group filter control to a group level that already has one, the previous control will change to a simple drop-down list box control.

  • To convert a group filter control to a regular control, right-click the control and click Group Filter Control. The control will change to a regular drop-down list box.

ShowCreate a filter in a Page to limit records after they are retrieved from the database

ShowCreate a filter in Page view to limit records after they've been retrieved

  1. Open a data access page in Page view.
  2. Do one of the following:

    ShowIn an ungrouped page

    1. Move focus to the field that has the value that you want to use to filter records.
    2. Click Filter By Selection Button image on the record navigation toolbar.

    ShowIn a grouped page

    1. Expand the group that you want to filter.

    2. Move focus to the field that has the value that you want to use to filter records.

    3. Click Filter By Selection Button image on the record navigation toolbar for that group.

      The filter applies only to the current group. In other words, if a page groups customer orders by country/region, and you filter the records for Brazil that have a required date of 6/2/98, that filter applies only to the records for Brazil, not to the records for the other countries/regions.

  3. To further narrow the subset of records, continue selecting fields and clicking Filter By Selection Button image until you have just the records you want.

    For example, if you want to see records only for customers in Germany with a required date of 5/12/98, select "Germany" in the Country field, click Filter By Selection, select "5/12/98" in the Required Date field, and then click Filter By Selection again.

Notes

  • If you select only part of the value in a field, the data access page still uses the entire field value for filtering.
  • You can remove the filter and then reapply it during the same working session.
  • This procedure doesn't apply to a PivotTable list, Spreadsheet Component, or Chart Component. These features can be filtered independently from the rest of the data on the data access page and may also support additional filtering capabilities. For more information, click Help Button image on that component's toolbar.

ShowCreate a hyperlink that filters the records on a data access page that the link jumps to

  1. Open a data access page in Design view.
  2. On the Insert menu, click Hyperlink.
  3. In the Insert Hyperlink dialog box, click Page in this database under Link to.
  4. In the Text to display box, type the text that you want to be displayed in the field or text box. If you leave the box blank, Microsoft Access will use the hyperlink address as the display text.
  5. Click ScreenTip and type the text that you want to appear when the user rests the pointer on the hyperlink. If you leave the ScreenTip text box blank, Access will display the hyperlink address as the ScreenTip.
  6. From the list, select the data access page you want to link to.
  7. To create a filter for the data access page, click Server Filter and type an expression in the form of a WHERE clause in the Filter criteria for the data access page box. For example, if you have a data access page that displays order information, including the ID of the salesperson, and you want to link to a data access page with the employee record about that salesperson, type EmployeeID=[EmployeeID].
  8. Click OK twice.

    Access adds the hyperlink to the data access page. To test the link, switch to Page view and click the hyperlink. Access opens the target page in your Web browser.

ShowFilter data in PivotTable or PivotChart view

ShowFilter data in PivotTable view

  1. Open a datasheet or form in PivotTable view.
  2. Do one or more of the following:

ShowFilter a field in the row, column, filter, or detail area (Autofiltering)

  1. Click the arrow Field arrow for the field that contains the items of data that you want to filter.

  2. Select the check boxes for the items you want to display, and clear the check boxes for the items you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the (All) check box. A shaded check box indicates that some of the lower-level items are hidden. To see which lower-level items are hidden, click the expand indicator Plus box beside the item with the shaded check box.

  • When placed in the row, column, or detail area, a field always supports multiple selection. A field in the filter area can support either single or multiple selection. To switch from multiple to single selection, on the Filter and Group tab of the Properties dialog box, clear the Allow selecting multiple items when in filter area check box.

ShowDisplay only data that matches a cell (Filter By Selection)

  • Right-click the cell that contains the value you want to display, and then click Filter By Selection on the shortcut menu.

ShowFilter a row or column field for top or bottom n values or a percentage (Conditional filtering)

  1. Select the field that has the values you want to filter.

  2. Click Properties Button image on the PivotTable toolbar, and then click the Filter and Group tab.

  3. Set Display the to Top or Bottom.

  4. Set Items to a number in the list, or type the value you want.

  5. To show n number of items, clear the % check box. To show n% of items, select the % check box.

  6. Set Based on to a value in the list.

Notes

  • When items in a field are hidden by an autofilter or a filter by selection, the arrow Field arrow in the field label is blue. When items are hidden by a conditional filter, the filtered field displays a funnel icon Icon image to the left of the arrow.
  • When you apply a filter, the displayed totals are based on visible items only. You can change the setting to include hidden data.

ShowFilter data in PivotChart view

ShowFilter a field in the category, series, or filter area (Autofiltering)

If the filter area does not have a field, you can either add a new field or move a field from another area to the filter area.
  1. Open a datasheet or form in PivotChart view.
  2. Click the arrow Field arrow for the field that contains the items of data that you want to filter.
  3. Select the check boxes for the items you want to display, and clear the check boxes for the items you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the (All) check box. A shaded check box indicates that some of the lower-level items are hidden. To see which lower-level items are hidden, click the expand indicator Plus box beside the item with the shaded check box.
  • When placed in the category, series, or filter area, a field always supports multiple selection. A field in the filter area can support either single or multiple selection.

ShowFilter a row or column field for top or bottom n values or a percentage (Conditional filtering)

  1. Open a datasheet or form in PivotChart view.
  2. Select the field, series, or point that has the values you want to filter.
  3. On the PivotChart toolbar, click Properties Button image, and then click the Filter and Group tab.
  4. In the Display the box, click Top or Bottom.
  5. Set Items to a number in the list, or type the value you want.
  6. To show n number of items, clear the % check box. To show n% of items, select the % check box.
  7. Set Based on to a value in the list.

Note  When items in a field are hidden by an autofilter, the arrow Field arrow in the field label is blue Blue arrow.

previous page start next page