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.
