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

ID: Q115902


The information in this article applies to:
  • Microsoft Access 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

Or you may receive the error message:
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*'").





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:

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

Additional query words: jet25 jet 2.5

Keywords : kberrmsg kbusage QryProb
Version : 2.0
Platform : WINDOWS
Issue type : kbbug


Last Reviewed: April 3, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.