Microsoft KB Archive/115902

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

Article ID: 115902

Article Last Modified on 7/8/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q115902



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



SYMPTOMS
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

<pre class="fixed_text">     ? 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*'") </li></ul>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Open the sample database NWIND.MDB.</li> Create a copy of the Employees table and save the table as Test.</li> Open the Test table in Design view.</li> From the View menu, choose Indexes.</li> Delete the existing index on the Last Name field.</li> Create a new index called NameKey. In the Field Name column, select Last Name.</li> 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.</li> Open any module, and then choose Immediate Window from the View menu.</li>  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.

<pre class="fixed_text">     ? DLookup("[Birth Date]", "Test", "[First Name] like 'L*' and _       [Last Name]='Callahan'") </li></ol>

<div class="references_section">