Percentage Errors with Access

jsawicki
jsawicki used Ask the Experts™
on
How come I can't obtain accurate decimal places for percentages within Access.  Below are some examples and I can't seem to understand the concept.  


1.  I generate these two and it gives me a whole number versus a decimal.  
SELECT  120/1953 AS percentage;
percentage
6.14439324116743E-02

SELECT  6/1953 AS percentage;
percentage
3.07219662058372E-03

2.  Then i generate these two and it gives the proper format
SELECT 84/1953 AS percentage;
percentage
0.043010752688172

SELECT 1743/1953 AS percentage;
percentage
0.89247311827957

I see the first two has an E-02 & E-03 at the end.  I have union query that calculates these 4 percentages from query fields and since they are all accurately written, i am not sure why the discrepancy.  I didn't post the code since i recreated it just using a basic division on what the sum numbers are in the queries.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try this


SELECT  format(120/1953,"percent") AS percentage
from tablex
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
They are as accurate as a Single can be, as that is the data type JET SQL falls back to when integers won't do it.

However, you can convert the calculations more precisely with CDec:

SELECT  CDec(CDbl(n/1953)) AS percentage;

This will return for your four examples:

 0.0614439324116744
 0.00307219662058372
 0.043010752688172
 0.89247311827957

/gustav

Author

Commented:
So i got Capricorns to work, but could not get it to work using Catcus' code.  Am curious on how i could get it to work using that code.  Below are the fields i am using and what variations i tried.   In my querey i actually want the hard numbers and then convert to percent in the report.  Cap: is there any other function that could be used instead of "percent" that would keep it in decimal point format?  

n=Count([Weekly Totals].LastUser) AS TotalUsers
1953=AllUsers.AllUsers

Below are the two things i tried:
CDec(CDbl([TotalUsers]/AllUsers.AllUsers)) AS [Percent]
CDec(CDbl(Count([Weekly Totals].LastUser)/AllUsers.AllUsers)) AS [Percent]
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use:

Format([YourValue], "0.##################")

/gustav
Top Expert 2016
Commented:
how many decimal?

this will give you 2 decimal places
SELECT  formatnumber(120/1953,2) AS percentage
from tablex

this will give you 4 decimal places
SELECT  formatnumber(120/1953,4) AS percentage
from tablex

Author

Commented:
Thanks all

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