Solved

How to prevent the error: Arithmetic overflow error converting expression to data type nvarchar ?

Posted on 2007-04-02
1
11,998 Views
Last Modified: 2013-12-05
I am developing an Access application with Accesss as the front end and SQL Server as the
backend database.

I have an existing table named tblMutualFunds comprised of the following fields:

MutualID int 4 (Identity)
State nvarchar (2)
Account nvarchar (9)

I also have an Excel file that one of my users created with the fields ResStateCode and Plan.

I use the TransferSpreadsheet command to Import the Excel file into an Access table named tblAppend.
My goal is to append the records from the imported Access table tblAppend into the table named tblMutualFunds.

I noticed that when the tblAppend table gets created, the fields generated are defined as follows:

ResStateCode  nvarchar(255)
[Plan #] float (8)

field ResStateCode of tblAppend correlates to field State on the table tblMutualFund
field [Plan #] of tblAppend correlates to field Account on the table tblMutualFund

When I try to perform the following

insert into dbo.tblMutualFunds
select ResStateCode, [Plan #]
from dbo.tblMuFunds

I get the error message:

Server: Msg 8115, Level 16, State 2, Line 27
Arithmetic overflow error converting expression to data type nvarchar.
The statement has been terminated.

The problem seems to be that the field [Plan #] gets converted from Excel into a Float field.
Now I am trying to insert values from table tblAppend for field Plan of type float
into a field Account of type nvarchar in table tblMutualFunds.

Do you know how I can fix this conversion problem ?
------------------------------------------------------------------------------------------
I was going to try to Alter the table tblMuFunds and alter the column [Plan #] to type nvarchar but I get the following error:

alter table dbo.tblMuFunds
alter column [Plan #] nvarchar not null

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
The statement has been terminated.
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18841419
I would first convert from float to decimal, int or bigint (depends on the data), and in a second step to nvarchar.
you might need to create another column with the intermediate data first...
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question