Subtracting two fields with same value returns "scientific value" instead of 0 (1 Viewer)

bobleb

New member
Local time
Today, 21:47
Joined
Nov 24, 2020
Messages
4
Hi Experts,

I am facing very weird issue here. I have a simple mathematical calculation which is resulting in scientific values instead of simple 0. Please refer the diff column in the below query and result:

Query
"
SELECT
code_combination,
YTD_ACCT_BAL,
NAT_ACCT_YTD_BAL_SUM,
TARGET_BALANCE,
(YTD_ACCT_BAL/NAT_ACCT_YTD_BAL_SUM) AS PERCENTAGE,
(TARGET_BALANCE-NAT_ACCT_YTD_BAL_SUM) AS Diff
FROM STAGING_DATA;
"

Result:
Query2 Query2

code_combinationYTD_ACCT_BALNAT_ACCT_YTD_BAL_SUMTARGET_BALANCEPERCENTAGEDiff
4150.30101.0000000000.00.4146.00000.0000.0000.000.0000.0000.000000
-11700000​
-11700000​
28935266.0026​
1​
40635266.0026​
1000.31101.0000000000.00.0000.00000.0000.0000.000.0000.0000.000000
-913750000​
-913750000​
-913750000​
1​
-1.19209289550781E-07​

the result in diff field in first row is correct however it not in second row. I have similar observation with other fields when the difference is 0. Can you please advise if I am missing some data type conversion or formatting?

Regards,
Bob
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
14,819
Hi Bob. This could happen if you're dealing with real numbers (the ones with decimal values), because computers cannot represent decimal values accurately in binary.

To get the results you're expecting, you may have to round your values first before doing math on them.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:47
Joined
Feb 19, 2002
Messages
31,653
Unless you need more than 4 decimal places of precision, your best bet for fields that you will do math with is Currency. Currency is BOTH a data type and a format. As a data type, it is logically a scaled integer. That means that it is always assumed to have four decimal places. Therefore, 1 = 10000 with an assumed decimal place four places from the left. It internally rounds to 4 decimal places and does not generate floating point errors such as what you are seeing.

You can display the currency data type as currency, percent, or other numeric format.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
12,278
covert first to Decimal.
create a function in a Module, and use the function for your Simple Calculation:

public function DecDiv(byval p1 as double, byval p2 as double, optional intRound as integer=-99) as variant
Dim decValue as variant
decValue = CDec(p1) / CDec(p2)
If intRount<>-99 then
decValue = Round(decValue, intRound)
end if
DecMul=decValue
end function

public function DecMul(byval p1 as double, byval p2 as double, optional intRound as integer=-99) as variant
Dim decValue as variant
decValue = CDec(p1) * CDec(p2)
If intRount<>-99 then
decValue = Round(decValue, intRound)
end if
DecMul=decValue
end function
 

bobleb

New member
Local time
Today, 21:47
Joined
Nov 24, 2020
Messages
4
Hi Bob. This could happen if you're dealing with real numbers (the ones with decimal values), because computers cannot represent decimal values accurately in binary.

To get the results you're expecting, you may have to round your values first before doing math on them.
Thanks for your inputs but as you see in my data there is no decimal values at all in the second row which is coming correctly while in the first row everything is fine with the decimal values. Rounding I am already aware of but dont want to use without understanding the real problem here.
Thanks.
 

bobleb

New member
Local time
Today, 21:47
Joined
Nov 24, 2020
Messages
4
Unless you need more than 4 decimal places of precision, your best bet for fields that you will do math with is Currency. Currency is BOTH a data type and a format. As a data type, it is logically a scaled integer. That means that it is always assumed to have four decimal places. Therefore, 1 = 10000 with an assumed decimal place four places from the left. It internally rounds to 4 decimal places and does not generate floating point errors such as what you are seeing.

You can display the currency data type as currency, percent, or other numeric format.
Thank you Pat, I will give it a try and see if there is any improvement in the result. My point is again same why Access is behaving different is these 2 rows.
Thanks.
 

bobleb

New member
Local time
Today, 21:47
Joined
Nov 24, 2020
Messages
4
covert first to Decimal.
create a function in a Module, and use the function for your Simple Calculation:

public function DecDiv(byval p1 as double, byval p2 as double, optional intRound as integer=-99) as variant
Dim decValue as variant
decValue = CDec(p1) / CDec(p2)
If intRount<>-99 then
decValue = Round(decValue, intRound)
end if
DecMul=decValue
end function

public function DecMul(byval p1 as double, byval p2 as double, optional intRound as integer=-99) as variant
Dim decValue as variant
decValue = CDec(p1) * CDec(p2)
If intRount<>-99 then
decValue = Round(decValue, intRound)
end if
DecMul=decValue
end function
Thank you for your inputs, I will use it but first I need to understand what is exactly is wrong here.
 

Minty

AWF VIP
Local time
Today, 21:47
Joined
Jul 26, 2013
Messages
8,270
Just because there is no displayed quotient, doesn't mean there isn't one.
As previously mentioned, if the original data type is float or real you can easily get tiny rounding error., which are only apparent when you subtract one from the other.

From the look of the field names, this is data pulled from an external SQL accounts database?
Can you get the data-types out of it?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:47
Joined
Feb 19, 2002
Messages
31,653
the result in diff field in first row is correct however it not in second row. I have similar observation with other fields when the difference is 0. Can you please advise if I am missing some data type conversion or formatting?
I don't see the results anywhere. perhaps you could post a database with just these tables and query?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:47
Joined
Feb 28, 2001
Messages
19,605
first I need to understand what is exactly is wrong here.

I like to try these "WHY" questions. Forgive me if I get a bit pedantic here.

In binary formats, scientific numbers (SINGLE and DOUBLE) do great with integers up to a point. Counting numbers do great up to a point. That point occurs when you run out of bits to represent your numbers. At that point, either overflow or truncation will occur.

With that in mind, let's discuss the fraction 1/10 (or 0.1 if you prefer.) To represent this number you have to divide 10 into 1. The number 10 has factors of 2 and 5, so 1/10 = 1/2 * 1/5. On a binary machine, 1/2 is TRIVIAL. But 1/5 is not - because it is not an even number. Just like the numbers 1/3 and 1/7 are infinitely long when expressed in decimal, the number 1/10 is infinitely long when expressed in binary. Of course, 1/3 = 0.333333... representing an unending string of the digit "3" in that result. The value of 1/7 = 0.142856142856...., endlessly repeating that 6-digit sequence. And the BINARY fraction of 1/10 is 0.0001100110011001100... repeating the 1100 pattern infinitely.

Problem is, your binary machine doesn't have infinite capacity. So that fraction has to stop at either 23 bits (SINGLE) or 53 bits (DOUBLE). When you truncate that infinite fraction, you introduce a representation error. Now when you have those two apparently equal numbers and subtract the difference between them, they are different somewhere in the 7th decimal place of the number (based on the E-7). Your "913750000" is 9 digits. The 7th decimal place makes the entire number 16 digits long, which tells me you have a DOUBLE. I.E. allowing for scaling, your numbers differ in the 16th place, which is the limit for DOUBLE numbers.

You would ask, "why don't the numbers cancel each out?" Well, they almost do - but if there was ANY DIFFERENCE in the way the two numbers were defined or provided or computed, that fractional difference will show up in DOUBLE numbers way down in the 15th or 16th decimal place. The difference between two identical numbers entered in the identical way SHOULD balance to zero. However, I note that your query says that one of the numbers is a balance and the other is a balance sum. If they have even the smallest difference in computational source, that is where you got rounded off or truncated.

NOW back to practicality. The suggestion to switch to CURRENCY as a data type is probably going to work just fine because, as noted, it is a CAST (a.k.a. TYPECAST) of a really long integer. Integers don't truncate the same way that scientific numbers do. They would prevent the rounding error.

Hope that tells you enough to satisfy you as to why this happened.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:47
Joined
Feb 19, 2002
Messages
31,653

Users who are viewing this thread

Top Bottom