Fixing Calculation in Query (1 Viewer)

[voxy]

 

[voxy]

Halo Everyone, Please Help me about this MisCalculation in my Query.
the sum of the sisastok should Be Zero

 

[The_Doc_Man]

OK, you suggest that you should get a zero for the named column. I can see that your field sisastok SEEMS to be based on the sign-adjusted values in fields Qty_terima and Qty_Keluar. But can you show us the SQL of the actual query? Also, what are the data types of the Qty_xxx columns?

When I do by hand what I think should be the correct computation by hand, it does balance. That means that what I am seeing there isn't based on the correct calculation. I.e. the "obvious" calculation is not occurring. So I have to ask you to show us what you told the computer to do for you.

 

[CJ_London]

suspect these are doubles and you are not seeing the whole number - suggest widen the column - you will probably see something like '-E15' at the end.

also suggest in your calculation you use the round function to round to 1 or 2 dp's

 

[The_Doc_Man]

Good point, CJ. I'm well aware of floating-point truncation issues but didn't consider that the number DISPLAYED was also visually truncated. Good catch.

 

[Pat Hartman]

suspect these are doubles

Given the number of decimal places in the sum, this is a sure thing

Unless you need more than four decimal digits, the best solution to avoid floating point errors (which this is) is to use the Currency data type. Currency is BOTH a format AND a data type. The currency data type can be formatted as any valid numeric format. It doesn't always have to look like currency with a symbol and two decimal places.

Currency behind the scenes is a scaled integer. That means that all numbers are assumed to have four decimal digits. So 33 is stored as 330000. Calculations are rounded to the fourth decimal place so 33 + 12.56713 = 485671

 

[voxy]

Given the number of decimal places in the sum, this is a sure thing

Unless you need more than four decimal digits, the best solution to avoid floating point errors (which this is) is to use the Currency data type. Currency is BOTH a format AND a data type. The currency data type can be formatted as any valid numeric format. It doesn't always have to look like currency with a symbol and two decimal places.

Currency behind the scenes is a scaled integer. That means that all numbers are assumed to have four decimal digits. So 33 is stored as 330000. Calculations are rounded to the fourth decimal place so 33 + 12.56713 = 485671

thank you Pat,

my Problem has been solved.

thanks guys

 

[Pat Hartman]

You're welcome