Microsoft KB Archive/234349

= ACC: Join Criteria Is Limited to 255 Bytes =

Article ID: 234349

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q234349



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



SYMPTOMS
In Microsoft Access 7.0 or in Microsoft Access 97, you try running a query with a long join criteria, and you find that the query does not return the expected number of records.



CAUSE
In Access 7.0 or in Access 97, the join criteria in queries is limited to 255 bytes. Records evaluated by queries that have join criteria exceeding 255 bytes are not returned in the result set.



STATUS
This is a limitation of Access 7.0 and Access 97. This limitation does not exist in Access 2000.



MORE INFORMATION
You cannot precisely determine the number of bytes used in a join criteria; however, you can roughly estimate the number of bytes by using the following algorithm:

One byte for each column in the join

+

One byte for each 256 characters in the record

+

Total bytes included in each column in the join.

Steps to Reproduce Behavior
 Create the following new table, and name it Table1:

Table: Table1

--

Field Name: Field1

Data Type: Text

Field Size: 10

Field Name: Field2

Data Type: Text

Field Size: 255

 Save the table, and click "No" when prompted if you want to let Access create a primary key. Add the following two records to the table. Note that Field2 in the second record should have a string 266 characters long.  Create a new query and add the Table1 table twice. Create a self join by joining Table1:Field1 to Table2:Field1 and Table1:Field2 to Table2:Field2.</li> Run the query. The expected result is that both records are returned, but instead only record one is returned. The record where the join criteria exceeds 255 bytes is not returned.</li></ol>

Additional query words: prb characters words acc97 acc95 missing incorrect not correct wrong results too long restricted

Keywords: kbprb KB234349

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.