ACC: Join Criteria Is Limited to 255 Bytes (234349)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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

  1. 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

  2. Save the table, and click "No" when prompted if you want to let Access create a primary key.
  3. Add the following two records to the table. Note that Field2 in the second record should have a string 266 characters long.
    Field1Field2
    data1abcdefghij
    data2 abcdefghIjklmnopqrstuvwxyzabcdefghIj
    klmnopqrstuvwxyzabcdefghIjklmnopqrst
    uvwxyzabcdefghIjklmnopqrstuvwxyzabcd
    efghIjklmnopqrstuvwxyzabcdefghIjklmn
    opqrstuvwxyzabcdefghIjklmnopqrstuvwx
    yzabcdefghijklmnopqrstuvwxyzabcdefgh
    IjklmnopqrstuvwxyzabcdefghIjklmnopqr
    stuokferspfsff
  4. Create a new query and add the Table1 table twice.
  5. Create a self join by joining Table1:Field1 to Table2:Field1 and Table1:Field2 to Table2:Field2.
  6. 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.
Modification Type:MajorLast Reviewed:10/22/2003
Keywords:kbprb KB234349
©2003 Microsoft Corporation. All rights reserved.