ACC: Error Using More Than 2000 Characters in WhereCondition of OpenReport

This article was previously published under Q184948
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

Symptoms
When you run a procedure that contains the OpenReport method of the DoCmdobject, and you use the WhereCondition argument to pass a string thatcontains more than 2000 characters, the report may not open. You may alsoreceive one of the following error messages:
- Run-time error '2465':

Microsoft Access can't find the field <field name> referred to in your expression.

You may have misspelled the field name, or the field may have been renamed or deleted.

- Run-time error '3464':

Data type mismatch in criteria expression.

- No current record.

- Out of memory.

Note that the number of characters in the WhereCondition argument thatcauses this behavior may vary. For example, the problem may not occur untilthe length of the WhereCondition argument exceeds 2048 characters.

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

Method 1

Instead of using the WhereCondition argument, use the FilterName argumentof the OpenReport method to pass the name of a saved query. This methodallows you to filter a report without limitation on the size of the WHEREclause.

Method 2

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

Note that the number of characters in a cell in the query grid is limitedto 1,024. If the SQL statement of the query results in more than 1,000characters in a cell in the query grid, and if you use the FilterNameargument to pass the name of that query to the OpenReport method, you maysee unexpected behavior.
Status
Microsoft has confirmed this to be a problem in Microsoft Access versions7.0 and 97.
More information

Steps to Reproduce Problem


  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Use the AutoReport: Columnar wizard to create a new report based on the Shippers table. Save the report as rptWhereTest.
  3. Create a new module, and type or paste the following procedure into the Module window:
          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         ' If you are using Microsoft Access 7.0, comment out the         ' previous line that contains the OpenReport method by typing         ' an apostrophe at the beginning of that line. Then remove         ' the apostrophe from the beginning of the following line:         ' DoCmd.OpenReport "rptWhereTest", acPreview, , strPWhr      End Sub						
  4. Compile and save the code.
  5. Run the procedure.

    Note that the code fails silently or that you receive one of the error messages described in the "Symptoms" section of this article.
References
For more information about the OpenReport method, search the Help Index for"OpenReport method," or ask the Microsoft Access 97 Office Assistant.
run time error 2465 can t run time error 3464
Properties

Article ID: 184948 - Last Review: 10/26/2013 14:51:00 - Revision: 4.0

  • Microsoft Access 97 Standard Edition
  • kbnosurvey kbarchive kbbug kberrmsg kbpending KB184948
Feedback