Solved

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

Posted on 2014-01-29
5
475 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

773 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