Create a filter (ADP)

Microsoft Office Access 2003

previous page next page

Note  You can't use Server Filter By Form with a form bound to a stored procedure.

  1. Open a form in Design view.
  2. Display the property sheet for the form.
  3. Do one of the following:
    • To enable Server Filter By Form, set the ServerFilterByForm property and AllowFilters property to Yes.
    • To disable Server Filter By Form, set the ServerFilterByForm property to No.
  4. Switch to Form view or the form's Datasheet view to test using Server Filter By Form.

Note  When you enable Server Filter By Form, Microsoft Access disables Filter By Form.

ShowUse Server Filter By Form to retrieve a subset of records for a form

When you are using Server Filter By Form, no data is retrieved for the form until you specify the values that you want the filtered records to contain. You can specify criteria for the form or subform that's displayed. Each subform has its own Look For and Or tabs.

  1. In the Server Filter By Form window, click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records.
  2. Enter your criteria by selecting the value that 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 it. 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 dimmed or unavailable.

    ShowFind records in which a particular field is empty or not empty

    • Type Is Null or Is Not Null into the field.

    Note  You can select these options from the list in fields with a Text or Image data type,and in calculated fields in queries. If the list in all fields displays Is Null and Is Not Null instead of values from the underlying table, the option to display lists in the Server Filter By Form window is cleared in the Options dialog box. You can change the Server Filter By Form settings to display field values in the list.

    ShowFind records by using a criteria expression

    • Type the expression into the appropriate field using SQL Server expression syntax.

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

  3. To specify alternative values that records can have, and include those values in the filter's results, click the Or tab for the form or subform you're filtering, and enter more criteria.

    The filter returns records if they have all the values that are 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.

  4. Click Apply Server Filter Button image on the toolbar.

Notes

  • Each time you apply the filter, Access automatically sets the form's ServerFilter property to a string expression that represents the filter that you defined in the Server Filter By Form window.
  • If you created a filter on a subform, that filter is also available when you open the form for the subform independently.
  • If you're using Server Filter By Form, you can't use Filter By Form.

ShowCreate a specific server filter that's automatically applied when a form or report is opened

You can't use a server filter with a form bound to a stored procedure.

  1. Open a form in Design view.
  2. Display the property sheet for the form
  3. Set the ServerFilter property to a string expression consisting of a WHERE clause without the WHERE keyword. For example, to find all the records where the Country field contains the value "USA," enter Country = 'USA'.

ShowCreate a filter to limit records in a data access page before they are retrieved from the database

Filtering records before they are retrieved from the database can improve data access page performance because there are fewer records to download from the underlying database.

  • You can base your page on a query that includes criteria (search conditions) to limit the records.

  • In a Microsoft Access database you can define a server-side filter in the ServerFilter property of the RecordsetDef object, or by using the Filter property in Visual Basic for Applications (VBA) or Microsoft Visual Basic Scripting Edition (VBScript). In a Microsoft Access project you can define a server-side filter in the ServerFilter property in VBA or VBScript.

  • On a grouped data access page, you can create a filter that retrieves data for a specific group, such as orders for a specific customer. The data access page won't display any records until you choose the group from a drop-down list in Page view or Microsoft Internet Explorer. Once you choose the group, the filter is applied and only those records are retrieved.

    On an ungrouped data access page, you can create a filter by designating a field as the filter field. When you choose a value from the filter field, all records that contain the selected value are retrieved.

  • In Design view, you can add a server-side filter to a hyperlink on a page. Then, when you click the link in Page view or in the browser, only the records specified in the filter are displayed on the page you jump to.

Limit records after they are retrieved from the database

ShowFilter by selecting values in a form or datasheet after records are retrieved (Filter By Selection)

  1. In a field on a form, subform, or datasheet, find one instance of the value that 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 "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.

      For example, 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".

    Note  You can't find records based on the end of the value in a field; however, if you select a word or part of a word that doesn't represent the last character of that field, records that contain that word or part of a word will be returned. For example, say the Pub_Name field contains the values "Five Lakes Publishing" and "Publishing Times." If you select "Publishing," then of the two records only "Publishing Times" will be returned. However, if you select "Pub" or "Publish," the filter will return both records.

  3. Select the value, and then 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.

Notes

  • 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.
  • If you created a filter on a subform, that filter is also available when you open the form for the subform independently.

ShowCreate a filter in Form view or Datasheet view (Filter By Form)

  1. Open a form in Form view, or a table, query, or form in Datasheet view.
  2. Click Filter By Form Button image on the toolbar to switch to the Filter By Form window. (If Server Filter By Form is enabled, Filter By Form will be disabled.)
  3. You can specify criteria for the form, subform, or datasheet that's displayed. Each subform has its own Look For and Or tabs.
  4. Click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records.
  5. Enter your criteria by selecting the value that 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 it. 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 dimmed or unavailable.

    ShowFind records in which a particular field is empty or not empty

    • Type Is Null or Is Not Null into the field.

      Note  You can select these options from the list in fields with a Text or Image data type,and in calculated fields in queries. If the list in all fields displays Is Null and Is Not Null instead of values from the underlying table, the option to display values in the Filter By Form window is cleared in the Options dialog box. You can change the Filter By Form settings to display field values in the list.

    ShowFind records by using a criteria expression

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

  6. To specify alternative values that records can have, and include those values in the filter's results, click the Or tab for the form, subform, or datasheet that you're filtering, and enter more criteria.

    The filter returns records if they have all the values that are 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.

  7. Click Apply Filter Button image on the toolbar.

ShowCreate a filter in Form view or Datasheet view by entering criteria in place (Filter For Input)

  1. Open a form in Form view, or a table, query, or form in Datasheet view.
  2. Do one of the following:

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

    • Right-click in the field in the form, subform, or datasheet 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

  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.

ShowEnable or disable filtering in a form

  1. Display the property sheet for the form.
  2. Set the AllowFilters property to Yes to enable filtering or to No to disable filtering capabilities in Form view and in form Datasheet view.

Note  When you set the AllowFilters property to No, you disable Filter By Selection, Filter By Form, Server Filter By Form, and Filter For Input.

ShowCreate a filter in a data access 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 a PivotTable or PivotChart view

ShowFilter data in a 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 a 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