Microsoft KB Archive/109354

From BetaArchive Wiki

PRA: Opening Multiple-Column Query Causes Clause Truncation

PSS ID Number: Q109354 Article last modified on 03-13-1995

1.00 1.10 2.00

WINDOWS

The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, and 2.0

SYMPTOMS

In Microsoft Access, each cell of the query grid has a character limit. However, you can use multiple columns in the Query-By-Example (QBE) grid to logically join clauses with an AND operator to form a larger query.

This technique works fine as long as you just run the query. However, if you save the query, then reopen it, the clauses will be combined into one statement that is truncated at the character limit as follows:

  • In Microsoft Access version 2.0, the character limit is 1024.
  • In Microsoft Access versions 1.0 and 1.1, the character limit is 255.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0, 1.1, and 2.0. We are researching this problem, and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The following sample query is longer than 255 characters, so Microsoft Access versions 1.0 and 1.1 will not allow the entire query in one column on the query grid:

Column 1: FieldN Not In (“arnold”, “burnett”, “crawford”, “drayton”, “edwards”, “flaherty”, “green”, “harvey”, “iverson”, “jackson”, “kelly”, “lawrence”, “morrison”, “nelson”, “opitz”, “quale”, “robertson”, “simonsen”, “thomas”, “underwood”, “vernon”, “woods”, “xavier”, “yonsen”, “zetterburg”)

To use this query, you could break the statement up into two Not In clauses against the same field (FieldN). The two statements would look like:

Column 1: FieldN Not In (“arnold”, “burnett”…“lawrence”)

Column 2: FieldN Not In (“morrison”, “nelson”…“zetterburg”)

This joins the clauses with a logical AND operator, producing the same result as if it were all one statement.

Additional reference words: 1.00 1.10 2.00 Queries KBCategory: kbusage KBSubcategory: QryOthr ============================================================================= Copyright Microsoft Corporation 1995.