"Numeric Field Overflow" error message occurs when you query a table that is linked to Excel spreadsheet

Office 2003에 대한 지원이 종료되었습니다.

Microsoft는 2014년 4월 8일 Office 2003에 대한 지원을 종료했습니다. 이러한 변경에 따라 해당 소프트웨어 업데이트 및 보안 옵션이 영향을 받습니다. 사용자에게 미치는 영향 및 계속 보호를 받는 방법에 대해 알아보십시오.

This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
For a Microsoft Access 2000 version of this article, see 815277.
SYMPTOMS
When you query a table that is linked to a Microsoft Excel spreadsheet, you may receive the following error message:

Numeric field overflow.
CAUSE
Access assigns a data type for each field of the Excel spreadsheet. This assignment is based on the data that is contained in the first eight rows. For example, if a field has a Number data type that is in the first eight rows and then has text values in some of the remaining rows, Access assigns the Number data type to the link table field. This causes Access to fail to link the records that have text data values. When you query this field, Microsoft Jet Database Engine encounters text where a number is expected. The query fails with the error message in the "Symptoms" section.
WORKAROUND
To work around this problem, you must make sure that the data values in each field of the source Excel spreadsheet are exactly the same data type. Or, if the fields of the Excel spreadsheet contains data values of a mixed data type, then format the field as Text. Use the following code to reenter the values in the cells. Then, link the Excel spreadsheet to Access. To do this, follow these steps:

Note Before you start these steps, you must backup your Excel spreadsheet.
  1. Open the source spreadsheet in Excel.
  2. In the spreadsheet, identify the fields that have data values of mixed data types.
  3. Select the identified columns and then format the cells in the spreadsheet as Text.
  4. Create a macro in Excel that contains the following procedure:
    Sub Addspace()  Dim cell As Object  For Each cell In Selection    cell.Value = " " & cell.Value    cell.Value = Right(cell.Value, Len(cell.Value) - 1)  NextEnd Sub
  5. Select the fields in the spreadsheet that contain data values of mixed data types. Run the macro that you created in step 4.

    Note If an error occurs when you run the macro on the problem cells, ignore the error.
  6. Save the spreadsheet.
  7. Open your database in Access.
  8. From your database, link to the source spreadsheet in Excel.
MORE INFORMATION
When you import, do not link an Excel spreadsheet. This is because the Text data type has a priority in the import algorithm. For example, if the predominant data type that is based on a sampling of data is numeric, but there is at least one text value in that sample, Access imports the whole field as Text.
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
282263 ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in Linked Tables
208414 ACC2000: #Num Appears in Linked Microsoft Excel Spreadsheet
For more information about how to create a macro in Microsoft Excel, click Microsoft Excel Help on the Help menu, type Create a macro in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For more information about how to run a macro in Microsoft Excel, click Microsoft Excel Help on the Help menu, type Run a macro in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
PRB reviewdocid
속성

문서 ID: 815277 - 마지막 검토: 12/08/2015 02:08:13 - 수정: 5.0

  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kberrmsg kbprb KB815277
피드백