General :: Scaling Of Decimal Value Resulted In Truncation?
Mar 22, 2013
I'm using Access 2003 to create some reports for an insurance company I work for by interrogating the Informix database of our policy maintenance software via an ODBC connection. The linked tables and the fields included within cannot be edited due to permission restrictions (the software is provided by a third party) but this doesn't usually cause issues.
However, I've recently created a Query that extracts a *number* field from one of our database tables, but I'm having trouble due to the 'Scale' attributed to this field in the Access table settings.
Access recognizes that the field is a 'Decimal' type, but attributes a 'scale' of '0' which causes an error. 'Scale' (for those who aren't aware) refers to the maximum number of digits that can appear to the right of a decimal separator, meaning whenever this value exceeds 0 (e.g. if an advisor enters something like 240.51 - a 'Scale' of '2'), the 'Scaling of decimal value resulted in truncation' error occurs.
As mentioned earlier, we don't have the ability to amend the Scale attributed to this value due to permission restrictions, although I'm told that 'Scale' is set in Access and not on the external database anyway.
how to bypass this issue? I can't reformat the field as Access attributes the Scale upon the initial linking of the table, long before the field is used in any queries. Permission restrictions also prevent me from amending the 'Scale' value in the linked table settings.
The software house themselves can't assist as they've set this value to hold multiple decimals on the actual database. It's as if Access only checks a small proportion of the fields held in the table (the first few hundred of this particular field are whole numbers) and attributes the scale based on this.
I get the above error when I try to insert text data into my Access 'password' column.Does the error mean that the field is too small to accommodate the data I am trying to insert into it? In Design View, it says 'Field Size: 50'. I am not sure what 50 is? 50 characters?Can I make that field size whatever size I wish?
I am using Access 2007.I have an application that keeps track of automotive parts and accessories.I have a table called "PARTS" that contains information about the various parts such as "part_name", "part_id", "price" etc.
I also have a website/shop that displays these various parts and also has an image of each part.I have managed to incorporate Microsoft Web Browser ActiveX functionality within the app so that when I scroll through the Parts table using the PartsForm, it automatically "reads" the URL from a field in the Parts table called "ImageURL" and displays it in the Microsoft Web Browser ActiveX browser window...this works great.
I've just successfully spitted a DB of 30+ MB of size to front end and back end. However the size of front end is roughly the original one and the size of back end is some KBs. Shouldn't have been the exact opposite considering that data are stored to the back end?
I have set up a form on which there is a graph which draws data from a query. I have set up a text boxes to take in the Y axis min, max and interval values so the user can customize the graph according tot he range coming out of the query. This all works fine and is perfect, however, i also need a report and set up a report with the same graph which can be printed to pdf, however, i cant get the y-axis to adjust like i do with the one on the form,
I already posted this as a response to another thread, but thought I should post it as a new one in case people don't follow threads...
I need to construct a SQL statement to insert a row. The VB code used to construct the insert statement is very long (the actual insert statement will be much shorter). For some reason that I do not understand, it is truncated after 255 bytes.
I created a Microsoft Access database that has a data entry form. This “parent” form has two sub-forms. Each sub-form can have many records that map to the same parent record.
I created VBA code to perform a dynamic query based on the fields I select and their associated values (e.g. query by form). The VBA code creates an SQL string that I use to run a query and present the results. I present the results via a report that looks just like my data entry form. The report includes two sub-reports (one for each of the sub-forms used in my data entry form).
On the main page of my report, there is a field called Gen_Comments. This field is of type “memo”. I see that the data in this field is truncated after 255 characters. This is not the case for any of the fields in my two sub-reports. My SQL statement uses the keyword DISTINCT. (Please refer to the SQL statement below). I’m pretty sure that this is what is truncating my memo field because if I remove this keyword and do a query, I do not get the truncation. However, if I remove this keyword, I then get duplicate records in my query.
HELP!! Can anyone determine if there is something wrong with my SQL statement below (which uses two nested inner joins)? Or, is there something else that I can do in conjunction with the DISTINCT keyword to eliminate the truncation? Any help would be GREATLY appreciated.
SELECT DISTINCT [PR_TABLE].*,SubTbl_DefectClassification.DefectNum,SubTbl_Ac tionItems.StepID FROM (([PR_TABLE] INNER JOIN SubTbl_DefectClassification ON PR_TABLE.ID = SubTbl_DefectClassification.DefectNum) INNER JOIN SubTbl_ActionItems ON PR_TABLE.ID = SubTbl_ActionItems.StepID) WHERE (((InStr(1,[PR_Table].PR_Gen_Comments,"The Wavecom",1))<> "0") AND (([SubTbl_DefectClassification].SevLevel) = "Minor") AND ((InStr(1,[SubTbl_ActionItems].Description,"Just a test",1))<> "0"))ORDER BY [PR_Table].ID
I sincerely thank you, in advance, for any help that you may be able to provide.
-Al Oberneder International Truck and Engine Corporation
I have a calculated field in a query that generates a result that i need to display in forms/reports with the $ sign but zero decimal places (ie; rounded to the nearest $ value, $5).
If i use the sql format currency function (format (n, "currency",0) it always displays the $ sign but with two decimal places...
In the form/report text box property i set the format to currency, with the decimal places set to 0, but it still displays two decimal places (this property setting seems to make no difference other than displaying the $ sign). Is there something i am missing regarding the way access formatting works?
I have a Table with a Field set to Number, Single, Fixed, 2 Decimal points in which I enter Hours (ie 11.25) then at some point I want to extract those new entries to create a Text file transfer.
I have a Macro which extracts those new entries from the main Table and copies/appends them to a new Table which contains only the new data I need to create the File to upload into a Payroll system (using TransferText option).
It all works well EXCEPT, the File it creates insists on showing 8 decimal points and I just cannot get it to show 2 only.I have tried using a calculated field, setting the secondary Table field to Text.why or where these 8 decimals are coming from.
I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%
I just linked a table to an Access Database from SQL Server. Now, whenever I open the linked table, I receive the error "Scaling of decimal value resulted in data truncation."
After a quick Google search (and search of these forums), I found some solutions:
what this means is that your number / data had a whole load of numbers after the decimal point, the length (width) of the field was not enough to display them all so some of the end was got lost
Increase the field size, or, limit the size of the number via the table, in design view, FIELD SIZE
It would appear that somewhere along the way you are implicitly converting from a decimal value that has high precision to an integer or other type value that has low precision. During the conversion their is some data loss in the form of lost precision.
So, I guess I found the problem, right? I just don't know what I'm suppose to do to correct the issue. There is not a single field in my SQL database that has a precision beyond 10 and a scale beyond 2. So...erm...Why can't Access just deal? What am I doing wrong?
I'm using a calculation on my form that subtracts one number from another and stores the difference between the two in a 3rd field. This works correctly but I want the difference to show 1 decimal place at all times.
So for instance if subtract 35.1 from 35.2 I get a difference of "0.1".
But if I subtract 35.0 from 36.0 I get a difference of "1".
The first case is fine but I need the difference to be "1.0" in the second case.
I've set the number of decimal places in the table from auto to 1 and changed the format on the form to a standard number with 1 decimal place. Neither of these show a difference of 1 as "1.0"
I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.
How do you get to see the entire file path in Access Linked Table Manager of linked tables that are located in a folder with a long path? The problem is that you can only view the first 64 or so characters of the table’s path. This is a real problem when these linked tables need to be updated and you don't know where it’s stored.
I am new to this forum and Access 2000. When creating a table I want to use a field to record hours worked to the tenth of an hour. When I enter 6.7 and tab to the next field the hours record as 7. I am using field size as integer, decimal as 1 and format as general number. I have tried numerous other combinations, but whatever decimal I enter rounds to show no decimal in the table. I will use this field in calculations. I suspect the solution is simple, but I am not having any luck. I'd appreciate some help. Gary4
I am using an inventory software program called ERP. I am trying to have at least 8 decimal places show in all my reports/tables/etc. I have set decimal places to 8 and 8 are showing up in some the reports BUT after the 4th decimal place, the other 4 decimals default to 0 (i.e. I put .19214, but only .19210000 shows up). In other tables only 4 decimal places are showing up (no 0 or anything after the 4th decimal place. I would greatly appreciate any help!!!
I have a table set up with a numeric field with 2 decimal places. When this table runs through a couple queries and comes out in a report - somehow the number is rounded. Where would the rounding occur - how can I stop it from happening?
Is there anyway in a query or a form to set the amount of decimal places on a number or change it to currency. In one query im dividing a price by a mileage and I get a really long string of number and I need to make it just be two decimals.
I have search the forum and cannot find an answer, can anyone please help. I have a few field in my table that have number and I would like to be able to enter decimal place numbers like 8.25.
I have set:
Field Size = 'Long Integer' Format = 'General Number' Decimal Places = '2'
When I go to enter a number it rounds to the whole so when I enter 8.25 it inputs 8.
Any ideas? I need the decimal to show in the query and the form and it isn't showing in any of those. I have set the query to 2 decimal places and form to 2 decimal places.
I want to but numbers with 2 decimal places in a field. I have the data type set to number in the desired field - yet when I copied and pasted the data over, all the numbers that had 2 decimal places lost them. The only number visible was the whole number. How can I make the numbers reatain their 2 decimal places?
