435,190 Members | 1,056 Online
Need help? Post your question and get tips & solutions from a community of 435,190 IT Pros & Developers. It's quick & easy.

# calculated field not calculating decimals correctly

 P: 48 I have a report card that calculates 6 fields and then takes the sum of these fields and substitutes it for a number grade. For example 5+4+3+4+5+5 = 26 The 26 is looked up in a table and the field displays 89. It seems to work fine when the scores are even numbers. However, teachers asked to be able to use whole and half grades between 0 and 5 (1, 1.5, 2, 2.5, etc). When the use a decimal, it often gives them a lower score - not sure if it isn't adding the decimals? An example: 4+4+4.5+3.5+4.5+5 = 25.5 in the lookup, that should generate a score of 88. However, instead it is taking the value for a score of 26 - giving the student an 89. It doesn't always round up though, sometimes it appears to round down and give a lower score than it should. Now the lookup table has all of the values and their equivalents. Here is a sample: 26.5 90 27 91 27.5 93 28 94 28.5 96 29 97 29.5 99 30 100 Here is the formula used: Expand|Select|Wrap|Line Numbers Private Sub Percent_GotFocus() Percent = [Understanding] + [Quality] + [Communication] + _              [Completion] + [Preparation] + [Participation]   Percent = Nz(DLookup("CalcPercent", "Percent_Comp_Table", "OrigPercent = " & Percent), 0) End Sub In the table, fieldtype - Number Field size - Long integer (I know, not needed, but this is what it was set to) Format - not set (though I tried general) Decimal - Auto Any thoughts on this would be appreciated! smg Mar 26 '10 #1

Integer & Long (integer) types are not good for handling decimal values.

Were the fields set up before the request to extend the possible values to fractional decimals?

5 Replies

 Expert Mod 15k+ P: 31,494 Integer & Long (integer) types are not good for handling decimal values. Were the fields set up before the request to extend the possible values to fractional decimals? Mar 26 '10 #2

 P: 48 Yes. And then once I made them decimals, I removed the field from the form and put it back in, not sure if I had to or not. Mar 29 '10 #3

 P: 48 I think I may have just figured out what was wrong. In the table, for the field where the calculation takes place, I had not set it to use decimals... so when adding the fields, I think it was dropping the decimal. Once I changed that, it seems to work. Mar 29 '10 #4

 P: 48 Yup, that was my issue. Once I made that field a Decimal field with a precision point of 5, it worked correctly. Mar 29 '10 #5

 Expert Mod 15k+ P: 31,494 Indeed. That was my guess as to the problem. I'm glad you managed to sort it out :) Mar 29 '10 #6