ACC2: "Can't Have More Than 10 Fields in an Index" Error Message (115902)

CAUSE

The "Can't have more than 10 fields in an index" error message can occur if all of the following conditions are true:

The underlying table contains a multiple-field index.
More than one field in the index is filtered by the query's criteria.
One of the criteria contains the EQUALITY (=) operator (for example, "='Joe'").
One of the criteria includes the LIKE operator with a wildcard (for example, "Like 'Smith*'").

The "Field won't fit" error message occurs because of small numeric indexes.

RESOLUTION

There are four workarounds for this behavior:

Upgrade the Microsoft Jet database engine from version 2.0 to 2.5.

Remove the multiple-field index on the underlying table and replace it with multiple single-field indexes. Note that you cannot use this method if the multiple-field index is the table's primary key. For example, change the multiple-field index

      Index Name   Field Name
      -----------------------
      NameIndex    Last Name
                   First Name

   to:

      Index Name   Field Name
      -----------------------
      LNameIndex   Last Name
      FNameIndex   First Name

Change the order of the fields in the two-field index on the underlying table. For example, change the two-field index

      Index Name    Field Name
      ------------------------
      Primary Key   Invoice #
                    Part #

   to:

      Index Name    Field Name
      ------------------------
      Primary Key   Part #
                    Invoice #

NOTE: In the following sample queries, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscores from the end of the line when re-creating these queries.

Replace the EQUALITY (=) operator with a LIKE operator and a wildcard. For example, change the query

      ? DLookup("[Birth Date]", "Employees", "[First Name] like 'L*' and _
        [Last Name]='Callahan'")

   to:

      ? DLookup("[Birth Date]", "Employees", "[First Name] like 'L*' and _
        [Last Name] like 'Callahan*'")

STATUS

This behavior no longer occurs with the Microsoft Jet database engine version 2.5, which is available with the Microsoft Access version 2.0 Service Pack. For information about how to obtain the Service Pack, please see the following article in the Microsoft Knowledge Base:

122927 WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database NWIND.MDB.
Create a copy of the Employees table and save the table as Test.
Open the Test table in Design view.
From the View menu, choose Indexes.
Delete the existing index on the Last Name field.
Create a new index called NameKey. In the Field Name column, select Last Name.
Select the next cell in the Index Name column. Leave the cell blank. In the Field Name column, select First Name. Close the Index dialog box.
Open any module, and then choose Immediate Window from the View menu.
Type the following line in the Immediate window, and then press ENTER.

NOTE: In the following line, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this line.
```
      ? DLookup("[Birth Date]", "Test", "[First Name] like 'L*' and _
        [Last Name]='Callahan'")
						
```