asked on

Percentage Errors with Access

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.

Rey Obrero (Capricorn1)

try this

SELECT format(120/1953,"percent") AS percentage
from tablex

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

jsawicki

ASKER

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]