# How does Access calculate?

I have a query that calculates discount prices using the expression below:

= list price * (1-discount)

If the list price is 14.43 & the discount is .27 the query is returning \$10.54.  I changed the format of the field to General Number & and it returned 10.5360898451507.  The the same calculation in Excel or on a calculator returns 10.5339.  Why is this happening?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
If you just trying to find the discount amount then...
Try this.....

discount = price * discount_amount

newprice = price - discount

--------
D_M_D
Commented:
do you hardcode .27 in your query? or does the .27 get pulled from another recordset?
same for your list price? have you hard coded it? or do you pull it from a recordset (eg. table or query)?

if you hardcoded both of them, then i dont know why you're getting what you're getting.
but if your source for either of these numbers is a table/query, then you should check how those numbers are dervied.
e.g. access may be only showing you 2 decimal places, where actually the filed supports more than 2 and the calculation also returned a value with more than 2 decimal place accuracy.

similarly, in excel, you coul've entered the value "1.23456" but you could set the decimal places to 2 and it'll display "1.23" in the cell. However, it does NOT get rid of the addtional decimal values (e.g. 456), they're still preserved as the part of the actual number.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
when I do the calculation you show, in a Query, using a field type of double, and Format as Currency for the value 14.42in a Table, I get the SAME value in Access 10.5339 as I get doing that calculation in Excel.  Something else is going on, that you are not elling us.

AW
Commented:
what is your data type for the field in access .. set it up to the number of decimal places you want .. it is rounding up the decimal places.

=============
Long Live the Penguin
=============

Cheers
******
Commented:
change from double to decimal
Author Commented:
Both fields are pulling from tables.  The list price field is setup as a Currency data type, auto decimal places.  The discount field is a setup as a Number data type, Single field size, 2 decimal places.

Commented:
if it's auto decimal places for currency .. then change it to 2... it should'nt have any effects!! right?? ... i think this is what's causing the problem.
Author Commented:
The list price displayed \$14.43, but the actual value stored in that field was 14.432989.... Thanks much!

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.