How to avoid error: Run-time error '3349' Numeric field overflow when executing a rountine in Access 2003 application?
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, ""
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, ""
Peter Chan
Check all columns one by one. It should be due to data mismatch there.
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
/gustav
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.
If you can't do that, then you may have to move to different methods to export to Excel.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
CASE WHEN LEN(C.DateOfBirth) = 0 THEN ' ' ELSE C.DateOfBirth END AS [Date Of Birth]
Also causes Numeric Overflow to occur
Also causes Numeric Overflow to occur