Solved

Query returns -4.61852778244065E-14 for a number field calculation

Posted on 2014-01-29
5
472 Views
Last Modified: 2014-02-04
I have an invoice table containing a number invoice lines, each with with 3 fields, Debit, Credit, and Amount.  All doubles to 2 dec places.  

Amount is positive for Debits and negative for Credits.  The Invoice looks perfectly normal in the Invoice table.  All values to 2 decimal places.

I have query which groups the transactions by invoice and sums the Amount which should be zero.

However, the query sometimes returns a Sum of Amount such as -4.61852778244065E-14, which makes the invoice out of balance.

Can anyone tell me how to fix this?

Thanks in advance.
0
Comment
Question by:CRB1609
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39819657
Floating point calculations are imprecise (period).

When you are comparing two floating point numbers you should avoid exact comparisons like checking if Field1 = Field2.

Instead, compare for numbers within acceptable 'tolerances'.

For example instead of Field1 = Field2, check for a tolerance such as:

Abs(Field1 - Field2) <  .0001
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 39819664
Regarding your 'Out of balance' issue... you can define a tolerance for your balance as well.

For example, define "Balanced" as somewhere between  -.00001 and + .00001, instead of strictly defining it as 0.
0
 

Expert Comment

by:marlind605
ID: 39819700
Have you tried the rounding when you calculate your number? This is From Microsoft How to Round a Number Up or Down
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 39819845
Change the datatype to Currency instead of double.

or use the ccur( ) conversion function to make the conversion in the query.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39820097
Yes. Cure your issue at the root. Listen to Dale and act. (No points please).

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 37
Explanation of Access VBA code 2 36
Two list boxes - best structure 3 32
What logic to build in order to get a weekly reminder 9 42
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now