Sum Expression gives 1.45519152283669E-11 instead of 0 (zero)

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

My query has Expression field AssetHolding wich sums asset's transactions
multiplied by current price and shows the asset's money-value held in
portfolio.

AssetHolding : Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates every
day an AssetHolding' criteria is <>0 (so that, when sold out, the Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange behaviour.

Today, having sold out one particular security, I've still recieved zero
value for this asset in the Portfolio form. When placing the mouse-pointer on
the AssetHolding cell the zero turned to microscopical sientifical value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset' name as
criteria (in order to limit the query to the asset in question), the query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get every
transaction' value record instead of its sum. Then I copied the results (1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero), with any
number of the decimal places (wich means the AssetHolding expression in the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of TransactionQuantity sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice part (the
only one wich can have fractional value) - the output became correct (the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a wonder!
When the running order of transactions was +68000 (buy), -60000 (sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so it became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became correct.

After all the check-ups I may assume that the problem arises when multiplier
with floating point is involved or when some particular order takes place in
the Sum function calculation. Still, neither of this makes any sense, as
there is no constant pattern in the query behaviour - for some assets it
happens, for others it does not.

What can be done? (other then bending the rebellious query artificially by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
 
Assuming TransactionQuantity is an integer, you can change the CurrentPrice
to a double precision float number (which is what it is, I assume), to a
currency number, as data type, at table design,or, alternatively, impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



Mishanya said:
My query has Expression field AssetHolding wich sums asset's transactions
multiplied by current price and shows the asset's money-value held in
portfolio.

AssetHolding : Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved zero
value for this asset in the Portfolio form. When placing the mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset' name
as
criteria (in order to limit the query to the asset in question), the query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get every
transaction' value record instead of its sum. Then I copied the results (1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero), with
any
number of the decimal places (wich means the AssetHolding expression in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice part
(the
only one wich can have fractional value) - the output became correct (the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes place
in
the Sum function calculation. Still, neither of this makes any sense, as
there is no constant pattern in the query behaviour - for some assets it
happens, for others it does not.

What can be done? (other then bending the rebellious query artificially by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
 
Hi Michel

Tried both of Your suggestions- to no effect.

Still, Your way is about "fooling" the Access around. Why is it happening in
the first place?

Michel Walsh said:
Assuming TransactionQuantity is an integer, you can change the CurrentPrice
to a double precision float number (which is what it is, I assume), to a
currency number, as data type, at table design,or, alternatively, impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



Mishanya said:
My query has Expression field AssetHolding wich sums asset's transactions
multiplied by current price and shows the asset's money-value held in
portfolio.

AssetHolding : Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved zero
value for this asset in the Portfolio form. When placing the mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset' name
as
criteria (in order to limit the query to the asset in question), the query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get every
transaction' value record instead of its sum. Then I copied the results (1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero), with
any
number of the decimal places (wich means the AssetHolding expression in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice part
(the
only one wich can have fractional value) - the output became correct (the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes place
in
the Sum function calculation. Still, neither of this makes any sense, as
there is no constant pattern in the query behaviour - for some assets it
happens, for others it does not.

What can be done? (other then bending the rebellious query artificially by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
Click to expand...
 
That is not due to Access itself, but to what is a floating point number.

Open the calculator ( Start / Accessories / Calculator ) and :

1.69 SQRT - 1.3 =


And you should get 9.11...E-39


That occurs because of the limited space to store (binary) digits, a little
bit like, by hand, you will represent 1 / 3 with a limited number of 3:
0.33333333333...

You just can't write 3 infinitely and when you stop, your representation is
NOT EXAXCTLY what it should.

Integers don't have this problem, neither Currency (and Decimal data type),
but have their own limitations.



In the problem at hand, I suspect, then, that TransactionQuantity is NOT an
integer, but also a floating point number. Try:


AssetHolding : Sum(Int(TransactionQuantity)*CreditDebit*CCur(CurrentPrice))



where Int( ) forces the floating point number to become an integer.

If CreditDebit is also a general floating point number, then it too, has to
be coerce into an integer:


AssetHolding :
Sum(Int(TransactionQuantity)*Int(CreditDebit)*CCur(CurrentPrice))



in order to avoid 'representation' error, at any point, in the process.



On the other hand, you are then limited to amounts not exceeding 1 Y$ by
much (one Yotta $ is 1E24 $ )



Vanderghast, Access MVP




Mishanya said:
Hi Michel

Tried both of Your suggestions- to no effect.

Still, Your way is about "fooling" the Access around. Why is it happening
in
the first place?

Michel Walsh said:
Assuming TransactionQuantity is an integer, you can change the
CurrentPrice
to a double precision float number (which is what it is, I assume), to a
currency number, as data type, at table design,or, alternatively, impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



Mishanya said:
My query has Expression field AssetHolding wich sums asset's
transactions
multiplied by current price and shows the asset's money-value held in
portfolio.

AssetHolding : Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates
every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved
zero
value for this asset in the Portfolio form. When placing the
mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical
value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset'
name
as
criteria (in order to limit the query to the asset in question), the
query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get
every
transaction' value record instead of its sum. Then I copied the results
(1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero),
with
any
number of the decimal places (wich means the AssetHolding expression in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice part
(the
only one wich can have fractional value) - the output became correct
(the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a
wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes
place
in
the Sum function calculation. Still, neither of this makes any sense,
as
there is no constant pattern in the query behaviour - for some assets
it
happens, for others it does not.

What can be done? (other then bending the rebellious query artificially
by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
Click to expand...
Click to expand...
 
Hi' Michel

First of all, thank You very much for Your detailed explanation.

Secondly, having posted the problematic formula I omitted one element
(CurrencyRate, wich normalizes all the assets as they come in different
currencies), thinking it is not importent and wanting to simplify the issue.
So the formula in whole is
AssetHolding:
Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice]*[CurrencyRate])
where TransactionQuantity and CreditDebit are Integers while CurrentPrice
and CurrencyRate are Doubles.

After I've changed both CurrentPrice and CurrencyRate into Currency type,
the "bug" was solved. So, THANK YOU VERY MUCH!

Thirdly, If You are still listening, I have one more question.
I get Your example with the SQRT - indeed the result may be infinitive
fraction.
But, in my case, CurrentPrice may have up to 4 decimal digits and
CurrencyRate - up to 2 decimal digits. So, in any case, the product may have
no more then 8 decimal digits max. How, then, can it overflow the limit
anyway?

Michel Walsh said:
That is not due to Access itself, but to what is a floating point number.

Open the calculator ( Start / Accessories / Calculator ) and :

1.69 SQRT - 1.3 =


And you should get 9.11...E-39


That occurs because of the limited space to store (binary) digits, a little
bit like, by hand, you will represent 1 / 3 with a limited number of 3:
0.33333333333...

You just can't write 3 infinitely and when you stop, your representation is
NOT EXAXCTLY what it should.

Integers don't have this problem, neither Currency (and Decimal data type),
but have their own limitations.



In the problem at hand, I suspect, then, that TransactionQuantity is NOT an
integer, but also a floating point number. Try:


AssetHolding : Sum(Int(TransactionQuantity)*CreditDebit*CCur(CurrentPrice))



where Int( ) forces the floating point number to become an integer.

If CreditDebit is also a general floating point number, then it too, has to
be coerce into an integer:


AssetHolding :
Sum(Int(TransactionQuantity)*Int(CreditDebit)*CCur(CurrentPrice))



in order to avoid 'representation' error, at any point, in the process.



On the other hand, you are then limited to amounts not exceeding 1 Y$ by
much (one Yotta $ is 1E24 $ )



Vanderghast, Access MVP




Mishanya said:
Hi Michel

Tried both of Your suggestions- to no effect.

Still, Your way is about "fooling" the Access around. Why is it happening
in
the first place?

Michel Walsh said:
Assuming TransactionQuantity is an integer, you can change the
CurrentPrice
to a double precision float number (which is what it is, I assume), to a
currency number, as data type, at table design,or, alternatively, impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



My query has Expression field AssetHolding wich sums asset's
transactions
multiplied by current price and shows the asset's money-value held in
portfolio.

AssetHolding : Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates
every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved
zero
value for this asset in the Portfolio form. When placing the
mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical
value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset'
name
as
criteria (in order to limit the query to the asset in question), the
query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get
every
transaction' value record instead of its sum. Then I copied the results
(1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero),
with
any
number of the decimal places (wich means the AssetHolding expression in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice part
(the
only one wich can have fractional value) - the output became correct
(the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a
wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes
place
in
the Sum function calculation. Still, neither of this makes any sense,
as
there is no constant pattern in the query behaviour - for some assets
it
happens, for others it does not.

What can be done? (other then bending the rebellious query artificially
by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
Click to expand...
Click to expand...
 
About your third question, the problem occurs because we use a base 10 and
the computer used a base 2. So, as example, 0.60000000.... in base 10, is
precisely represented since the period, the infinite sequence of decimal
digits, is 0. But the same number, written in base 2, does not have a period
of 0.

In base 10: 0.abc... == a *(1/10) + b*(1/100) + c*(1/1000) + ....
with a,b,c, ... digits between 0 and 9 included

in base 2: 0.mnpop... == m *(1/2) + n * (1/4) + o * (1/8) + p *(1/16)
with m, n, o, p ... digits between 0 and 1 included,
or if you prefer, either 0, either 1


so, 0.6 base 10 = 6 *(1/10) + 0 * (1/*100) + 0 *(1/1000) + 0* ...

converted in base 2:
= 1 * (1/2) + 0 * (1/4)
+ 0 * (1/8) + 1 * ( 1/16)
+ 1 * ( 1/32) + 0 * ( 1/64)
+ 0 * (1/128) + 1 * (1/256) + ...

( if you add the displayed fraction, you should get 0.59765625, not
yet strictly equals to 0.6 )

= 0.10011001....


So, while 0.6, base 10, is precise, it is when we store it into a base 2
computer that we make a representation error. That is why a floating point
number is sometimes referred to an appreciative value. Sure, the IEEE
standard is much more complicated than that, but the principle used by IEEE
standard for floating point number is similar.




Vanderghast, Access MVP



Mishanya said:
Hi' Michel

First of all, thank You very much for Your detailed explanation.

Secondly, having posted the problematic formula I omitted one element
(CurrencyRate, wich normalizes all the assets as they come in different
currencies), thinking it is not importent and wanting to simplify the
issue.
So the formula in whole is
AssetHolding:
Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice]*[CurrencyRate])
where TransactionQuantity and CreditDebit are Integers while CurrentPrice
and CurrencyRate are Doubles.

After I've changed both CurrentPrice and CurrencyRate into Currency type,
the "bug" was solved. So, THANK YOU VERY MUCH!

Thirdly, If You are still listening, I have one more question.
I get Your example with the SQRT - indeed the result may be infinitive
fraction.
But, in my case, CurrentPrice may have up to 4 decimal digits and
CurrencyRate - up to 2 decimal digits. So, in any case, the product may
have
no more then 8 decimal digits max. How, then, can it overflow the limit
anyway?

Michel Walsh said:
That is not due to Access itself, but to what is a floating point number.

Open the calculator ( Start / Accessories / Calculator ) and :

1.69 SQRT - 1.3 =


And you should get 9.11...E-39


That occurs because of the limited space to store (binary) digits, a
little
bit like, by hand, you will represent 1 / 3 with a limited number of 3:
0.33333333333...

You just can't write 3 infinitely and when you stop, your representation
is
NOT EXAXCTLY what it should.

Integers don't have this problem, neither Currency (and Decimal data
type),
but have their own limitations.



In the problem at hand, I suspect, then, that TransactionQuantity is NOT
an
integer, but also a floating point number. Try:


AssetHolding :
Sum(Int(TransactionQuantity)*CreditDebit*CCur(CurrentPrice))



where Int( ) forces the floating point number to become an integer.

If CreditDebit is also a general floating point number, then it too, has
to
be coerce into an integer:


AssetHolding :
Sum(Int(TransactionQuantity)*Int(CreditDebit)*CCur(CurrentPrice))



in order to avoid 'representation' error, at any point, in the process.



On the other hand, you are then limited to amounts not exceeding 1 Y$ by
much (one Yotta $ is 1E24 $ )



Vanderghast, Access MVP




Mishanya said:
Hi Michel

Tried both of Your suggestions- to no effect.

Still, Your way is about "fooling" the Access around. Why is it
happening
in
the first place?

:

Assuming TransactionQuantity is an integer, you can change the
CurrentPrice
to a double precision float number (which is what it is, I assume), to
a
currency number, as data type, at table design,or, alternatively,
impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



My query has Expression field AssetHolding wich sums asset's
transactions
multiplied by current price and shows the asset's money-value held
in
portfolio.

AssetHolding :
Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates
every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved
zero
value for this asset in the Portfolio form. When placing the
mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical
value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset'
name
as
criteria (in order to limit the query to the asset in question), the
query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get
every
transaction' value record instead of its sum. Then I copied the
results
(1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero),
with
any
number of the decimal places (wich means the AssetHolding expression
in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of
TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice
part
(the
only one wich can have fractional value) - the output became correct
(the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a
wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so
it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes
place
in
the Sum function calculation. Still, neither of this makes any
sense,
as
there is no constant pattern in the query behaviour - for some
assets
it
happens, for others it does not.

What can be done? (other then bending the rebellious query
artificially
by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
Click to expand...
Click to expand...
 
So, I gather, any time I want to omit zero records in query, I'll have to
examine whether fractional values are involved in calculations, even if there
is common multiplier wich sums up to zero (in my case -
DebitCredit*TransactionQty).
Did not know that, never met any reminding of this in Access books, Help or
forums.

Thank You very much indeed, Michel.

Michel Walsh said:
About your third question, the problem occurs because we use a base 10 and
the computer used a base 2. So, as example, 0.60000000.... in base 10, is
precisely represented since the period, the infinite sequence of decimal
digits, is 0. But the same number, written in base 2, does not have a period
of 0.

In base 10: 0.abc... == a *(1/10) + b*(1/100) + c*(1/1000) + ....
with a,b,c, ... digits between 0 and 9 included

in base 2: 0.mnpop... == m *(1/2) + n * (1/4) + o * (1/8) + p *(1/16)
with m, n, o, p ... digits between 0 and 1 included,
or if you prefer, either 0, either 1


so, 0.6 base 10 = 6 *(1/10) + 0 * (1/*100) + 0 *(1/1000) + 0* ...

converted in base 2:
= 1 * (1/2) + 0 * (1/4)
+ 0 * (1/8) + 1 * ( 1/16)
+ 1 * ( 1/32) + 0 * ( 1/64)
+ 0 * (1/128) + 1 * (1/256) + ...

( if you add the displayed fraction, you should get 0.59765625, not
yet strictly equals to 0.6 )

= 0.10011001....


So, while 0.6, base 10, is precise, it is when we store it into a base 2
computer that we make a representation error. That is why a floating point
number is sometimes referred to an appreciative value. Sure, the IEEE
standard is much more complicated than that, but the principle used by IEEE
standard for floating point number is similar.




Vanderghast, Access MVP



Mishanya said:
Hi' Michel

First of all, thank You very much for Your detailed explanation.

Secondly, having posted the problematic formula I omitted one element
(CurrencyRate, wich normalizes all the assets as they come in different
currencies), thinking it is not importent and wanting to simplify the
issue.
So the formula in whole is
AssetHolding:
Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice]*[CurrencyRate])
where TransactionQuantity and CreditDebit are Integers while CurrentPrice
and CurrencyRate are Doubles.

After I've changed both CurrentPrice and CurrencyRate into Currency type,
the "bug" was solved. So, THANK YOU VERY MUCH!

Thirdly, If You are still listening, I have one more question.
I get Your example with the SQRT - indeed the result may be infinitive
fraction.
But, in my case, CurrentPrice may have up to 4 decimal digits and
CurrencyRate - up to 2 decimal digits. So, in any case, the product may
have
no more then 8 decimal digits max. How, then, can it overflow the limit
anyway?

Michel Walsh said:
That is not due to Access itself, but to what is a floating point number.

Open the calculator ( Start / Accessories / Calculator ) and :

1.69 SQRT - 1.3 =


And you should get 9.11...E-39


That occurs because of the limited space to store (binary) digits, a
little
bit like, by hand, you will represent 1 / 3 with a limited number of 3:
0.33333333333...

You just can't write 3 infinitely and when you stop, your representation
is
NOT EXAXCTLY what it should.

Integers don't have this problem, neither Currency (and Decimal data
type),
but have their own limitations.



In the problem at hand, I suspect, then, that TransactionQuantity is NOT
an
integer, but also a floating point number. Try:


AssetHolding :
Sum(Int(TransactionQuantity)*CreditDebit*CCur(CurrentPrice))



where Int( ) forces the floating point number to become an integer.

If CreditDebit is also a general floating point number, then it too, has
to
be coerce into an integer:


AssetHolding :
Sum(Int(TransactionQuantity)*Int(CreditDebit)*CCur(CurrentPrice))



in order to avoid 'representation' error, at any point, in the process.



On the other hand, you are then limited to amounts not exceeding 1 Y$ by
much (one Yotta $ is 1E24 $ )



Vanderghast, Access MVP




Hi Michel

Tried both of Your suggestions- to no effect.

Still, Your way is about "fooling" the Access around. Why is it
happening
in
the first place?

:

Assuming TransactionQuantity is an integer, you can change the
CurrentPrice
to a double precision float number (which is what it is, I assume), to
a
currency number, as data type, at table design,or, alternatively,
impose
cast explicitly the value into a currency number:

AssetHolding : Sum(TransactionQuantity*CreditDebit*CCur(CurrentPrice))




Vanderghast, Access MVP



My query has Expression field AssetHolding wich sums asset's
transactions
multiplied by current price and shows the asset's money-value held
in
portfolio.

AssetHolding :
Sum([TransactionQuantity]*[CreditDebit]*[CurrentPrice])

where CreditDebit may be 1 or -1 (buy or sell), CurrentPrice updates
every
day an AssetHolding' criteria is <>0 (so that, when sold out, the
Portfolio
form, based on the query, won't show zero holdings).

It had worked fine untill today, when I encountered very strange
behaviour.

Today, having sold out one particular security, I've still recieved
zero
value for this asset in the Portfolio form. When placing the
mouse-pointer
on
the AssetHolding cell the zero turned to microscopical sientifical
value
1.45519152283669E-11.

I've run some checks:
1) In the query indeed the AssetHolding is presented as 0 (or
1.45519152283669E-11 when mouse-pointed), but when I put the asset'
name
as
criteria (in order to limit the query to the asset in question), the
query
output is null (no records).
2) In the query I've added TransactionNumber field in order to get
every
transaction' value record instead of its sum. Then I copied the
results
(1
buy and 2 sells) to Excel and sumed it up. The result was 0 (zero),
with
any
number of the decimal places (wich means the AssetHolding expression
in
the
query should return excactly 0 with no fractions when sum of
TransactionQuantity bought is equal to the sum of
TransactionQuantity
sold).
3) In the AssetHolding expression I've eliminated the CurrentPrice
part
(the
only one wich can have fractional value) - the output became correct
(the
sold-out asset disappeared).
4) Finally I've swaped the sell-transactions' quantities. What a
wonder!
When the running order of transactions was +68000 (buy), -60000
(sell), -8000
(sell), as it should have been, I had this annoying zero or
1.45519152283669E-11. But having swapped the sell transactions, so
it
became
+68000 (buy), -8000 (sell), -60000 (sell), the query output became
correct.

After all the check-ups I may assume that the problem arises when
multiplier
with floating point is involved or when some particular order takes
place
in
the Sum function calculation. Still, neither of this makes any
sense,
as
there is no constant pattern in the query behaviour - for some
assets
it
happens, for others it does not.

What can be done? (other then bending the rebellious query
artificially
by
putting some IIF((Sum(x)) <.0000000001,0,Sum(x)) criteria)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top