Percentage field in Access Database

Conernesto
Conernesto used Ask the Experts™
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
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

Open in new window

Only use Single for special cases where you now it will work.
Mark EdwardsChief 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?
Data-Type-Calculations.PNGHope this helps explain things...
Mark EdwardsChief Technology Officer

Commented:
By the way, here's the Windows Calculator result.  I wonder what data type they used here?
Calculator-Value.PNG
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Mark EdwardsChief Technology Officer

Commented:
Here's the result of Gustav's recommendation for percent value (Currency) used for the percentage field.
Percent-as-Currency-Calculations.PNGLooks like he has the issue well in hand...
Mark EdwardsChief 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...
Percent-as-Double-Calculations.PNGWhat'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.)

Author

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

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial