ACC2000: Query Expression Truncated at 511 Characters

ID: Q239565

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

In a Microsoft Access 2000 query, when you try to edit the query in the query design grid, you may receive the following error message:

The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (|).

You then notice that an expression or a critera has been truncated.

Upon saving the query, you may also see the following error message:

Missing ),] or Item in query expression '<expression>'.

CAUSE

An expression or a criteria in your query is over 511 characters in length. Although Access 2000 Help states that a cell in a query design grid has a limit of 1024 characters, the Access 2000 query design interface actually has a limit of 511 characters. This issue does not occur in earlier versions of Microsoft Access.

RESOLUTION

Do not try to edit an expression of an existing query in Design view if your SQL statement has more than 511 characters.
If you want to create a query with an expression that exceeds 511 characters, you can use the CreateQueryDef method in a Visual Basic for Applications procedure. The following example illustrates how to do this with the example from the "Steps to Reproduce Problem" section:

Open the sample database Northwind.mdb.

Click Modules under Objects, and then click New.

In the new module type the following code:


Option Compare Database

Sub CreateLongQuery()

   Dim db1 As DAO.Database
   Dim qdfNew As DAO.QueryDef

   Set db1 = CurrentDb

   With db1
      Set qdfNew = .CreateQueryDef("LongExpQuery", _
         "SELECT Employees.EmployeeID, [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] AS Expr1 FROM Employees;")
      .Close
   End With

   Application.RefreshDatabaseWindow

End Sub

In the Immediate window, type the following line, and then press ENTER:


CreateLongQuery

A new query called LongExpQuery appears in Access.

The query that you created in this example can be run, but its expression cannot be edited through the interface if it has more than 511 characters. To make changes that require an expression greater than 511 characters, create a new query with the changes you need by once again using the CreateLongQuery procedure.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

If the query has been imported or converted from an earlier version of Access, it will run but you cannot edit it in Design view.

Steps to Reproduce Problem

Open the sample database Northwind.mdb.

Create a new query and add the following field:


   Field: EmployeeID
   Table: Employees

In the Field line of the next column, type the following expression:


   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname]

On the Query menu, click Run.

Note that you receive the following error:

Missing ),] or Item in query expression '<expression>'.

Additional query words: pra

Keywords : kberrmsg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug