ACC: Query with Criteria Causes ApplyFilter to Fail Silently
ID: Q149067
|
The information in this article applies to:
SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.
When you specify a query that has a criteria as the FilterName argument of
an ApplyFilter action, an ApplyFilter method, an OpenForm action, or an
OpenForm method, the filter is not applied, and you receive no error
message.
CAUSE
Microsoft Access does not set the Filter property of the form if you use a
query as a filter and the length of the WHERE clause in the query exceeds
256 characters.
RESOLUTION
There are two possible workarounds. Both use Visual Basic for Applications.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to the "Building
Applications with Microsoft Access for Windows 95" manual.
Method 1
You can simulate a filter by setting a form's record source to a query that
has criteria. For example, if your query that has criteria is called Query1, set the form's RecordSource property to Query1 by using the following sample code:
Me.recordsource="Query1"
You can use this technique in different event procedures, such as the
form's Load event or a command button's Click event.
Method 2
You can use the query's SQL WHERE clause (without the word WHERE) in the
Where Condition argument of the ApplyFilter method or OpenForm method. The
Where Condition argument for these methods has a physical limit of 32,768
characters.
Here are the basic steps. This example uses the sample database
Northwind.mdb:
- Open the sample database Northwind.mdb.
- Use the following SQL statement to create a new query based on the
Order
Details table:
SELECT DISTINCTROW [Order Details].OrderID, [Order
Details].ProductID, [Order Details].UnitPrice, [Order
Details].Quantity, [Order Details].Quantity FROM [Order Details]
WHERE ((([Order Details].OrderID)>10500) AND (([Order
Details].ProductID)=31 Or ([Order Details].ProductID)=12 Or ([Order
Details].ProductID)=56 Or ([Order Details].ProductID)=14) AND
(([Order Details].UnitPrice)>5) AND (([Order Details].Quantity)>5))
OR ((([Order Details].UnitPrice)>100)) OR ((([Order
Details].Quantity)>120)) OR ((([Order Details].Quantity)>100));
- Close and save the query as TestQuery.
- Use the AutoForm: Columnar Wizard to create a new form based on the
Order Details table.
- Open the new form in Design view.
- Create a command button, and then view its properties.
- Set the button's OnClick property to the following event procedure:
DoCmd.ApplyFilter "TestQuery", "((([Order Details].OrderID)>10500)" & _
" AND (([Order Details].ProductID)=31 Or " & _
"([Order Details].ProductID)=12 Or ([Order Details].ProductID)=56" & _
" Or ([Order Details].ProductID)=14) AND " & _
" (([Order Details].UnitPrice)>5) AND " & _
" (([Order Details].Quantity)>5))" & _
" OR ((([Order Details].UnitPrice)>100)) OR " & _
" ((([Order Details].Quantity)>120))" & _
" OR ((([Order Details].Quantity)>100))"
- Switch the form to Form view. Note that there are 2155 records in the
form.
- Click the command button to apply the filter. Note that there are 139
records in the form after the filter is applied.
NOTE: This technique only works with the ApplyFilter or OpenForm methods in
Visual Basic. You cannot use an ApplyFilter or OpenForm action in a macro
to work around this problem because the WhereCondition argument for these
actions is limited to 256 characters in Macro Design view.
WARNING: If the WHERE clause contains text criteria in quotation marks, the
quotation marks will also need to be concatenated into the SQL statement.
For more information about concatenating strings, please see the following
article in the Microsoft Knowledge Base:
Q136059
ACC: Errors Concatenating Variables or Controls (2.0/95)
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access
version 7.0. This problem no longer occurs in Microsoft Access 97.
MORE INFORMATION
Steps to Reproduce Problem
- Open the sample database Northwind.mdb.
- Use the following SQL statement to create a new query:
SELECT DISTINCTROW [Order Details].OrderID, [Order
Details].ProductID, [Order Details].UnitPrice, [Order
Details].Quantity, [Order Details].Quantity FROM [Order Details]
WHERE ((([Order Details].OrderID)>10500) AND (([Order
Details].ProductID)=31 Or ([Order Details].ProductID)=12 Or ([Order
Details].ProductID)=56 Or ([Order Details].ProductID)=14) AND
(([Order Details].UnitPrice)>5) AND (([Order Details].Quantity)>5))
OR ((([Order Details].UnitPrice)>100)) OR ((([Order
Details].Quantity)>120)) OR ((([Order Details].Quantity)>100));
- Close and save the query as TestQuery.
- Use the AutoForm: Columnar Wizard to create a new form based on the
Order Details table.
- Open the new form in Design view.
- Create a command button, and then view its properties.
- Set the button's OnClick property to the following event procedure:
DoCmd.ApplyFilter "TestQuery"
- Switch the form to Form view. Note that there are 2155 records in the
form.
- Click the command button to apply the filter. Note that no filter was
applied; there are still 2155 records in the form.
REFERENCES
For more information about the SQL WHERE clause, search on the phrase
"where condition," and then view "WHERE Clause (SQL)" using the
Answer Wizard from the Microsoft Access for Windows 95 Help menu.
Additional query words:
Keywords : kbusage
Version : WINDOWS:7.0
Platform : WINDOWS
Issue type : kbbug