ACC: Join Criteria Is Limited to 255 Bytes

• Microsoft Access versions 7.0, 97

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.




Field1	Field2
data1	abcdefghij
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.

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

Keywords : kbdta
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb