Decimal Numbers stored in a table are not accurate.

[MatthewGrace]

I created a table with a field called PercentOfFormula. Datatype is Single (wasn't sure what to use - this could be the problem).



I'm storing chemical formulas have 10% of this, 20% of that, and even fractions of a percent (ie .35%) of some trace ingredients. Since I needed the decimal to represent these fractions of a percent, I decided to use the aforementioned Single datatype.

After plugging in all my many ingredients and their percentages (only 2 fields in this easy table), I expected everything to add up to 100% when I did a quick check. This would confirm I entered everything correctly. I ran a Totals query using Sum() on that field since I didn't want to do it by hand. To my surprise, everything adds up to something like "0.999999968800694". This is REALLY weird because I never used more than 4 digits to the right of a decimal.

My guess is that its something to do with the datatype. I tried the Decimal datatype but it seemed like it only accepted whole numbers for some reason. Maybe I'm doing something wrong. It's been a long time since I've used Access.

A quick and dirty solution would be appreciated, although, I am willing to do a deep dive into the understanding of datatypes if you think it's prudent that I do so.

Thank you for any help,
Matt

[ranman256]

If you only need 4 decimals,use currency. It has a limit of 4 decimals, just be sure to set the format to remove the $ sign.

[Bob Fitz]

Setting the field properties to:

Field Size: Decimal
Format: Fixed
Decimal Places: 4

should give the desired result.

[davegri]

I suggest using Decimal and specify scale and precision properties in the table.Precision is the total number of digits allowed in a number, including left and right of the decimal.
Scale is the number of digits allowed to the right of the decimal.


So 1234567.89 has a precision of 9.
123456.789 has a precision of 9 and a scale of 3.


A precision of 4 and scale of 2 would result in a number with a maximum value of 99.99


Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.


Precision has a maximum value of 28, but textboxes in forms allow only a max of about 15 decimal places.

[MatthewGrace]

Thank you all for your advice. I appreciate it.
matt