Solved

How does Access calculate?

Posted on 2003-11-14
8
319 Views
Last Modified: 2010-04-17
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?
0
Comment
Question by:4044
8 Comments
 
LVL 10

Expert Comment

by:D_M_D
ID: 9750916
If you just trying to find the discount amount then...
Try this.....

discount = price * discount_amount

newprice = price - discount


--------
D_M_D
0
 
LVL 9

Accepted Solution

by:
malharone earned 100 total points
ID: 9751268
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9752159
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Expert Comment

by:Depeche_Mode
ID: 9752225
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
******
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 9761523
change from double to decimal
0
 

Author Comment

by:4044
ID: 9763294
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.

0
 
LVL 9

Expert Comment

by:malharone
ID: 9765058
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.
0
 

Author Comment

by:4044
ID: 9765660
The list price displayed $14.43, but the actual value stored in that field was 14.432989.... Thanks much!

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Plain Text Editor for iPad 6 95
Math Equation 23 105
Identify two keyboard codes and how to enter them 9 53
ejb message driven bean mdb creation steps 2 15
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question