Hi
ACC2000: Exporting to Excel May Cause "Numeric Field Overflow" Error
View products that this article applies to.
This article was previously published under Q223225
Novice: Requires knowledge of the user interface on single-user computers.
This article applies to a Microsoft Access database (.mdb) and to a
Microsoft Access project (.adp).
SYMPTOMS
When you export a table that contains a date earlier than 01/01/1900 to
Microsoft Excel, you may encounter one of the following problems:
In a Microsoft Access project (.adp), one of the following occurs:
You see the following error message
Numeric field overflow.
and the resulting Excel worksheet does not contain any data.
-or-
You see the following error message
Exceptions tables could not be created on import/export.
and the date field in the Excel worksheet is null.
In an Access database (.mdb), you do not receive an error message, but the
date field in the Excel worksheet is null. In a database, an errors table
is also generated, marking the records that contain unrecognized dates with
Date Out of Range.
CAUSE
Microsoft Excel does not recognize dates earlier than January 1, 1900.
RESOLUTION
Exporting date values earlier than 1/1/1900 to Excel always causes the
fields that contain unrecognized dates to be blank. However, you can avoid
the errors that you receive when you export from an Access project by
upgrading to SQL Server 7.0 or Microsoft Data Engine to Service Pack 1.
For additional information about downloading the latest service pack,
please see the following article in the Microsoft Knowledge Base:
301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack
MORE INFORMATION
Steps to Reproduce Behavior in an Access Database
Open the sample database Northwind.mdb.
Open the Employees table and change the birth date of Andrew Fuller to
December 1, 1899.
On the File menu, click Export.
In the Export Table 'Employees' to dialog box, click Microsoft Excel
97-2000(*.xls) in the Files of Type box.
Name the file Test.xls, and then click OK.
Close the Employees table.
In the Database window, click Tables under Objects.
Note that the table, Employees_ExportErrors, has been generated.
Open the Employees_ExportErrors table and view the error record.
for more information visit:
http://support.microsoft.com/?kbid=223225
Numeric Range
Numeric range determines the sensitivity of the spreadsheet to overflow and
underflow errors. Excel stores numbers between -1.798 x10 +308 and 1.798 x
10+308 for a numeric range of ±10+308. A scientific calculator handles a
range of 10±99 and the CRAY 1 has a range of 10±2500. Although the largest
number that Excel can store is 1.798 x 10+308, the largest number that you
can type is 9.999 x 10+307. If you type in a larger number, Excel will
treat it as a character string.
Most computational science calculations have reasonable results somewhere
in the range of 10-40 to 10+40. When these numbers are used in an
equation, the intermediate results are often quite large. If the
intermediate results exceed the range of the computer then an overflow
condition will return an error.
Error Numbers
Excel treats seven error values as if they were numbers:
DIV/0! Division by zero
#NAME A variable name in a formula has not been defined
#N/A No value is available
#NULL! A result does not exist, or is an invalid intersection of two
areas
#NUM! Numeric overflow, underflow, or incorrect use of a number, such
as SQRT(-1).
#REF! Invalid cell reference; the cell is not on the worksheet
#VALUE! Invalid argument type, such as text where a number is required
--------------------
From: DoctorV <<
[email protected]>>
Subject: Numeric Overflow Error
Date: Tue, 27 Apr 2004 11:57:20 -0500
Message-ID: <
[email protected]>
Organization: ExcelTip
User-Agent: ExcelTipForum
X-Newsreader: ExcelTipForum
X-Originating-IP: 63.240.15.100
Newsgroups: microsoft.public.excel
NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
Lines: 1
Path: cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11
.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.excel:18749
X-Tomcat-NG: microsoft.public.excel
I have an Access Database linked to an Excel Worksheet. In my attempts
to massage the data, I changed their refernces from Right 2 characters
to DatePart. Now I consistently am getting a Numeric Overflow error?
Can someone explain this?
[/QUOTE]
Thanks
Koushik R
Microsoft
Global Partner Support
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this
message are best directed to the newsgroup/thread from which they
originated.
