Create a filter (ADP)

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.

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.

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

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

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

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

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.

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.

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

   Find 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".

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

1. Open a form in Form view, or a table, query, or form in Datasheet view.
2. Click Filter By Form 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.

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

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

   Find records by using a criteria expression

   • Type the expression into the appropriate field.

   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 on the toolbar.

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

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

   Specify more complex criteria

   • Type the complete expression.
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.

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.