ACC2: "Can't Have More Than 10 Fields in an Index" Error Msg
ID: Q115902
|
The information in this article applies to:
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
- 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'")
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