ACC: Saving Filter By Selection/Form Doesn't Limit Recordset
ID: Q152364
|
The information in this article applies to:
-
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 when you use 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
- Open the sample database Northwind.mdb.
- Click the Queries tab, and then click New.
- 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.
- 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.
- 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.
- 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).
- Click Save As/Export on the File menu.
- In the New Name box, type Filter Test and then click OK.
- Close the Datasheet view.
- 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.
- 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.
- 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.
- The query should display the same number of records that were displayed
in Step 6.
REFERENCES
For more information about filter by selection or filter by form, search
the Help Index for "Filter by Selection" or "Filter by Form."
Keywords : kbusage QryAppnd
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb