"Error scaling of decimal value resulted in data truncation" Error (1 Viewer)

DAPOOLE · May 24, 2012

Hi there,

I have a SQL Server linked table with a NUMERIC(5,2) datatype and a trigger on that table firing on an Insert or Update which all appears to be working as required as I can use SQL Query Analyzer to view the correct data being stored in the table. The problem is that on creating a database front end in Access using linked tables to link to this table whenever I try to insert or update the data in that table using a form I am receiving the error "Error scaling of decimal value resulted in data truncation".

Can anyone help me resolve this error so I can view the correct decimal data via the form?

Thanks in advance.

gemma-the-husky · May 24, 2012

is there a visible decimal point, or is the dp implied in the SQL format.

maybe you need to treat the "value" as text, and mainpulate it to a number

I would use currency to avoid any real number rounding issues.

try either

ccurr([funny field])
or
ccurr([funny field]) /100

see which one gets the dp correct.

DAPOOLE · May 24, 2012

Thanks, but I am doing all calculation in a trigger on the SQL Server end not Access. All I am using Access for is as a front-end to the data. The column definition of the linked table in SQL Server is NUMERIC(5,2). Access doesn't seem to want to display the data despite it being correct in the SQL Server.

gemma-the-husky · May 24, 2012

as i say, that storage format may be a character format, not a true binary number format. in which case access needs to treat it as a string, not as a number

i assume you have linked to the sql table - so what field type does it show in the table definition? maybe you are just losing the values after the dp?

DAPOOLE · May 24, 2012

Thanks, but I'm not sure what you mean by changing the storage to text. If I do this through Access to try to amend the table storage definition well it won't allow me to save the changes as it's a linked table. But then if I try to change the datatype of the underlying table in SQL Server well then when I try to recreate the SQL Server trigger on that table it fails with the "Invalid operator for data type. Operator equals multiply, type equals text." error.

As stated before the data is being correctly stored and amended from within SQL Server by the table and trigger however it is Access that will not show the decimal data.

gemma-the-husky · May 24, 2012

I do not know what you are seeing

i assume you have an access dbs, with a link to the table/view in the SQL data.

if so, design the access table, and see what it says the datatype is for the field in quesion. you will not be able to change it, but you will be able to see it.

what does it say it is?

DAPOOLE · May 24, 2012

The linked table within SQL Server has a datatype of 'NUMERIC(5,2)', and when I go into Access then open the same table up in design view I can see that the datatype mentioned there is 'Number'. Of which the parameters under the 'General' tab are: Field Size is 'Decimal', Precision is '18', Scale is '0', and Decimal Places is 'Auto'. If I try to change this data type in Access, say to 'Text', I get the 'Microsoft Office Access can't save property changes for linked tables' pop up.

DAPOOLE · May 28, 2012

Can anyone help to get the decimal values in these tables to correctly show on the Access front-end? Thanks.

gemma-the-husky · May 28, 2012

ok - your table is defined as decimal number

if you display the table, what does the data look like? do you see the decimal point or not?

maybe with this format you have to divide/multiply the number by 100 to "scale" up the value to reflect the actual/implied value of the dp.

surely one of these will get to what you want

ccurr(storedfield)

ccurr(storedfield/100)

ccurr(storedfield*100)/100

or ccurr(storedfield)/100

DAPOOLE · May 28, 2012

Thanks. Although I can see through SQL Query Analyzer that the table is correctly calculating and storing the value with a decimal, whenever I try to open up the database front-end in Access to view the form or even the table data I get the "Error scaling of decimal value resulted in data truncation" error popping up across the screen. After which I need to Ctl-Alt-Del to kill the application. To open up the application I actually need to delete the decimal data in Query Analyzer then restart the Access front-end. If I was to use ccurr(storedfield) as you recommend then how would I go about it? Thanks again.

DAPOOLE · May 31, 2012

Finally worked out the solution. Whenever dealing with linked tables in Access and you change the underlying table definition when creating your front-end you MUST relink your linked tables for the changes to propagate to Access. In my case I had changed the linked tables data definition to from numeric to numeric(5,2) but hadn't relinked the tables, so on entering or calculating decimal values Access didn't know that it could store them, hence the error "Error scaling of decimal value resulted in data truncation".

Always relink after making underlying changes.

"Error scaling of decimal value resulted in data truncation" Error (1 Viewer)

DAPOOLE

Registered User.

gemma-the-husky

Super Moderator

DAPOOLE

Registered User.

gemma-the-husky

Super Moderator

DAPOOLE

Registered User.

gemma-the-husky

Super Moderator

DAPOOLE

Registered User.

DAPOOLE

Registered User.

gemma-the-husky

Super Moderator

DAPOOLE

Registered User.

DAPOOLE

Registered User.

Similar threads

Users who are viewing this thread