ACC2: "Can't Have More Than 10 Fields in an Index" Error Msg

• Microsoft Access version 2.0

SYMPTOMS

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

When you use a domain aggregate (totals) function or run a query, you may receive the following error message:

   Can't have more than 10 fields in an index

   Field won't fit

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*'").

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 #
  
  

• 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:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Create a copy of the Employees table and save the table as Test.

3. Open the Test table in Design view.

4. From the View menu, choose Indexes.

5. Delete the existing index on the Last Name field.

6. Create a new index called NameKey. In the Field Name column, select Last Name.

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

8. Open any module, and then choose Immediate Window from the View menu.

9. 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'")
   
   

REFERENCES

For more information about the error message "Can't have more than 10 fields in an index" search for "error messages: reference" using the Microsoft Access Help menu.