asked on

Percentage field in Access Database

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?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Edwards

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...

Mark Edwards

By the way, here's the Windows Calculator result. I wonder what data type they used here?

Mark Edwards

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...

Mark Edwards

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.)

Conernesto

ASKER

Thank you both for all the feed back.

Gustav Brock

You are welcome!