ACC: Saving Filter By Selection/Form Doesn't Limit Recordset

• Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you save the results of a query after you apply additional filters using Filter By Selection or Filter By Form, the saved recordset consists of all records that were returned before you applied the additional filters.

CAUSE

Any criteria you apply using Filter By Selection or Filter By Form is saved in the Filter property of the query. When you open the query again, it shows all the records. The Filter property is not applied until you click the Apply Filter button on the toolbar or click Apply Filter/Sort on the Records Menu.

RESOLUTION

To ensure that only the necessary records are saved with the new query, you need to manually add all additional filtering as criteria within the QBE grid or the SQL Select statement.

MORE INFORMATION

Any field names and values chosen when you use Filter By Selection or Filter By Form will be stored with the query's Filter property. This filter only affects the output of the query in memory (when applied), but does not modify the Select statement. When you click Save As/Export on the File menu, Microsoft Access, by design, saves the query's actual Select statement (the query's design), not the results of what the query holds in memory.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Click the Queries tab, and then click New.

3. Create a query using the Customers table and the Simple Query Wizard. Include all the fields in the query and save it as Customers Query.

4. Click the Customers Query, and then click Open.

   Note the number of records being displayed on the status bar. For example, it may read Record 1 of 91. This number will be important later.

5. Press the TAB key until the field value in the Contact Title column is highlighted (approximately three times).

   NOTE: This field value will most likely be Sales Representative. However, the field's actual value is not important.

6. Click Filter on the Records menu, and then click Filter By Selection.

   NOTE: The status bar will now display a different number. For example, it may display Record 1 of 17 (filtered).

7. Click Save As/Export on the File menu.

8. In the New Name box, type Filter Test and then click OK.

9. Close the Datasheet view.

10. Click Filter Test, and then click Open.

    NOTE: The datasheet's status bar displays the same number of records that was noted in Step 4. All original records are included and not excluded as expected. The criteria that you applied was saved in the Filter property.

11. If you click the Apply Filter button, you will get the "Enter Parameter Value" prompt. This is because the query name was saved with the Filter property. Click Cancel, and then click Query Design on the View Menu. Click the background of the query, and then click Properties on the View Menu.

12. The Filter property contains (([Customers Query].Contact Title="Sales Representative")). Modify this property so that is says (([Filter Test].Contact Title="Sales Representative")). Click Save on the File Menu, and then click Datasheet on the View Menu.

13. The query should display the same number of records that were displayed in Step 6.

REFERENCES