• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • 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
CRB1609
Asked:
CRB1609
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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