Knowledge Base

ACC2000: Error Using More Than 2000 Characters in Wherecondition of OpenReport

Article ID: 207615

Article Last Modified on 12/12/2002

Microsoft Access 2000 Standard Edition

This article was previously published under Q207615

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you run a procedure that contains the OpenReport method of the DoCmd object, and you use the wherecondition argument to pass a string that contains more than 2000 characters, the report may not open. You may also receive the following error message:

Run-time error '7769':

The filter operation was canceled. The filter would be too long.

Note that the number of characters in the wherecondition argument that causes this behavior may vary. For example, the problem may not occur until the length of the wherecondition argument exceeds 2048 characters.

RESOLUTION

You can use two methods to filter a report without limitation on the size of the WHERE clause.

Method 1

Instead of using the wherecondition argument, use the filtername argument of the OpenReport method to pass the name of a saved query. This method allows you to filter a report without limitation on the size of the WHERE clause.

Method 2

Use the Open event to set the RecordSource property of the report to a SQL statement or to a saved query.

Note that the number of characters in a cell in the query grid is limited to 1,024. If the SQL statement of the query results in more than 1,000 characters in a cell within the query grid, and if you use the filtername argument to pass the name of that query to the OpenReport method, you may see unexpected behavior.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Problem

Start Access and open the sample database Northwind.mdb.
Use the AutoReport: Columnar wizard to create a new report based on the Shippers table. Save the report as rptWhereTest.

Create a new module and enter the following procedure:

Sub ReportWhereTest()
    Dim strPWhr As String

    'Build a Where clause that is 2046 characters long.
    strPWhr = "[Phone] in ('" & String(2031, "a") & "')"

    'Show how long the WhereCondition is.
    MsgBox Len(strPWhr) & " Characters"

    'Open the report with the WhereCondition.
    DoCmd.OpenReport "rptWhereTest", acViewPreview, , strPWhr
End Sub

Compile and save the code.
Run the procedure.

Note that the code will fail silently, or you will receive the error message described in the "Symptoms" section of this article.

REFERENCES

For more information about the OpenReport method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type OpenReport method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: pra run time error 7769

Keywords: kberrmsg kbbug kbnofix kbdta KB207615