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.
Clive BeatonAccess DeveloperAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
Change the datatype to Currency instead of double.

or use the ccur( ) conversion function to make the conversion in the query.
0
 
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
 
mbizupConnect With a Mentor Commented:
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
 
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.

All Courses

From novice to tech pro — start learning today.