ACC: Query with Criteria Causes ApplyFilter to Fail Silently

 Last reviewed: August 28, 1997
Article ID: Q149067
The information in this article applies to:
  • Microsoft Access version 7.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you specify a query with criteria as the FilterName argument of an ApplyFilter action, ApplyFilter method, OpenForm action, or 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 using 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 with criteria. For example, if your query with criteria is called Query1, set the form's RecordSource property to Query1 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 using the sample database Northwind.mdb:

  1. Open the sample database Northwind.mdb.

  2. Create a new query based on the Order Details table using the following SQL statement: 

          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));

  3. Close and save the query as TestQuery.

  4. Create a new form based on the Order Details table using the AutoForm: Columnar Wizard.

  5. Open the new form in Design view.

  6. Create a command button, and then view its properties.

  7. Set the button's OnClick property to the following event procedure (using the code builder): 

        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))"

  8. Switch the form to Form view. Note that there are 2155 records in the form.

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

   ARTICLE-ID: Q136059
   TITLE     : 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

  1. Open the sample database Northwind.mdb.

  2. Create a new query using the following SQL statement: 

          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));

  3. Close and save the query as TestQuery.

  4. Create a new form based on the Order Details table using the AutoForm: Columnar Wizard.

  5. Open the new form in Design view.

  6. Create a command button, and then view its properties.

  7. Set the button's OnClick property to the following event procedure (using the Code builder): 

          DoCmd.ApplyFilter "TestQuery"

  8. Switch the form to Form view. Note that there are 2155 records in the form.

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

Keywords          : kbusage
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbbug
Solution Type     : kbfix

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.