We help IT Professionals succeed at work.

How to avoid error: Run-time error '3349'  Numeric field overflow when executing a rountine in Access 2003 application?

zimmer9
zimmer9 asked
on
Medium Priority
4,026 Views
Last Modified: 2014-09-23
I am developing an Access 2003 application using an ADP type file with SQL Server as the back end database.

I have a routine in my application that executes a stored procedure dbo.procFlToLN which produces a result set into a table titled "dbo.tblFlToLN".

As for the file layout of the fields in table tblFlToLN
there Data Types are all "nvarchar" except 1 field that is of Data Type "datetime":

When my routine executes the following statement after the stored procedure,
I get the error:

Run-time error '3349':
Numeric field overflow.

Do you know how I can resolve this issue?
DoCmd.TransferSpreadsheet acExport, 8, "dbo.tblFlToLN", ExportedFile, True, ""
Comment
Watch Question

Peter ChanProblem resolver

Commented:
Check all columns one by one. It should be due to data mismatch there.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You probably have at least one field which contains digits only. Even though the field type is Text, it may be exported as a number. If so, you may try to prefix it with a single quote: '

/gustav
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you do have columns that contain numbers in a Text column, you could also try creating a View with an Order By clause, and make sure that actual Text values are included in the first few rows. I believe that Excel looks at the first 8 rows to determine what sort of values it should be showing, but that value can be changed, so the more you can put at the top, the better off you are.

If you can't do that, then you may have to move to different methods to export to Excel.

Author

Commented:
Here is my stored procedure. Is there anything here that noticeably could be causing the Numeric overflow condition to occur?


SELECT DISTINCT
CASE WHEN C.TaxIDInd = '1' THEN ' ' ELSE UPPER(C.FirstName) END AS [1st First Name],
UPPER (C.MiddleInitial) AS [1st Mid],
CASE WHEN C.TaxIDInd = '1' THEN C.FirstName ELSE UPPER(C.LastName) END AS [1st Last Name],
UPPER(C.SecondNameFirst) As [2nd First Name], UPPER(C.SecondNameMid) AS [2nd Mid], UPPER(C.SecondNameLast) AS [2nd Last Name],
UPPER(C.StreetAddr1) AS [Address 1], UPPER (C.StreetAddr2) AS [Address 2],
UPPER(C.City) AS City,
UPPER(C.ResStateCode) AS [State], C.Zip AS Zip, CASE WHEN LEN(C.SSN) = 0 THEN C.TaxID ELSE C.SSN END AS [SSN/Tax ID],  
Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], C.DateOfBirth AS [Date Of Birth]
INTO dbo.tblFlToLN
FROM dbo.tblStatesAll AS S INNER JOIN dbo.tblCustomers AS C ON S.StateFS = C.ResStateCode
                              INNER JOIN dbo.tblProducts AS P ON C.CustomerNumber = P.CustomerNumber  AND C.OfficeNumber = P.OfficeNumber
WHERE (S.FallCycle= 1) AND C.RedFlag = 'N' AND LEN(P.InsuranceContract) = 0
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
I'd suspect one of these:

Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
C.DateOfBirth AS [Date Of Birth]

You might try using CAST or CONVERT in the Stored Procedure to force the values to a VarChar field, although the Excel export can still interpret those as Numbers. Or as Gustav indicates, preface it with a single quote:

' ' ' + Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], C.DateOfBirth AS [Date Of Birth]

I've included some spaces to show you what I've done, but you don't need them in your query.

Author

Commented:
I discovered it's the date field that is causing an issue.

When I modify my stored procedure - date field to include:

' ' AS [Date Of Birth]

Then the Excel file is created successfully.


However, when I modify my stored procedure - date filed to include:

CASE WHEN LEN(C.DateOfBirth) = 0 THEN NULL ELSE C.DateOfBirth END AS [Date Of Birth]

Then I get the Numeric field oveflow

Do you know how I can modify the date field so that I can pick up the value from C.DateOfBirth when it has a value?

Author

Commented:
CASE WHEN LEN(C.DateOfBirth) = 0 THEN ' ' ELSE C.DateOfBirth END AS [Date Of Birth]

Also causes Numeric Overflow to occur