• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

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
Clive Beaton
Asked:
Clive Beaton
2 Solutions
 
mbizupCommented:
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
 
mbizupCommented:
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
 
marlind605Commented:
Have you tried the rounding when you calculate your number? This is From Microsoft How to Round a Number Up or Down
0
 
Dale FyeCommented:
Change the datatype to Currency instead of double.

or use the ccur( ) conversion function to make the conversion in the query.
0
 
Gustav BrockCIOCommented:
Yes. Cure your issue at the root. Listen to Dale and act. (No points please).

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now