# Percentage field in Access Database

on
I have a database with a field in a table used for percentages. The filed name is called Percentage. I set the data type to Number, field size to Single, Format to Fixed and decimal places to four.

I am multiplying a value times Percentage and I don't get the right total. The value is 2,578,364,462 times the percentage of .30. The answer I am getting is 773,509,369. I should be getting 773,509,338. Can you tell me what needs to be changed to get the right total?
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes. Use (always) Currency as the  data type for fields holding amount or quantities:

``````n = 2578364462
p = CCur(.30)

? n * p
773509338,6

n = 2578364462
p = CSng(.30)
? n * p
773509369,3365
``````
Only use Single for special cases where you now it will work.
Chief Technology Officer

Commented:
The result you get depends on the data type you use.  The image below shows some examples of taking your number 2,578,364,462 as several different numeric data types in a table and multiplying them by .3 (30%) which is a single data type.  The result is the % columns in the image.
As you can see, only the Decimal data type gives you 773509338, Double and Currency give you 773509369, and Single gives you 773509376.  Hmmmm….. which is the right one?
Hope this helps explain things...
Chief Technology Officer

Commented:
By the way, here's the Windows Calculator result.  I wonder what data type they used here?
Chief Technology Officer

Commented:
Here's the result of Gustav's recommendation for percent value (Currency) used for the percentage field.
Looks like he has the issue well in hand...
Chief Technology Officer

Commented:
One last post:  Here's the results from using a Double as the percent data type, in case you don't like the way Currency looks...
What's interesting is that if you convert the Single to a Double or Currency, you get 2578364416, NOT 2578364462 (thus the diff between Single and all the others.)

Commented:
Thank you both for all the feed back.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with