Hi
I am writing a financial database that tracks Stocks, Bonds and Cash in several currencies.
To do this I have a table with a record for each transaction that includes a field for [Quantity]. I have another table with [CurrentValue] for each asset. Both of these are number - Double - 4 Decimal Place fields. I can't use Currency due to it always displaying the $ sign.
I am generating a report to show the [Quantity], [CurrentValue] and [TotalValue] for each Asset.
The recordsource for this report is a totals query joining the Transaction and Current Value tables. It returns [Quantity] and [CurrentValue] as well as calculating the [TotalValue] as [Quantity] * [CurrentValue].
In order to balance this report with bank accounts I have to follow the same methodology as the bank when doing the multiplication, which is to round down decimal places after 2 e.g., ###.1199 gets rounded to ###.11
To do this I wrote a custom function RndDn () that looks like:
I thought this was adequate until this morning when I encountered a line on the report that read:
142.266 * 10 = 1,422.65 ==> Should, obviously, = 1422.66
I discovered that I can get the correct result by re-writing the function as:
So, I'm wondering if anybody can tell me why the two are different and, especially, why the first one is returning 1422.65?
Thanks
A/R
--I attached a simplified db to demonstrate.
I am writing a financial database that tracks Stocks, Bonds and Cash in several currencies.
To do this I have a table with a record for each transaction that includes a field for [Quantity]. I have another table with [CurrentValue] for each asset. Both of these are number - Double - 4 Decimal Place fields. I can't use Currency due to it always displaying the $ sign.
I am generating a report to show the [Quantity], [CurrentValue] and [TotalValue] for each Asset.
The recordsource for this report is a totals query joining the Transaction and Current Value tables. It returns [Quantity] and [CurrentValue] as well as calculating the [TotalValue] as [Quantity] * [CurrentValue].
In order to balance this report with bank accounts I have to follow the same methodology as the bank when doing the multiplication, which is to round down decimal places after 2 e.g., ###.1199 gets rounded to ###.11
To do this I wrote a custom function RndDn () that looks like:
Code:
Public Function RndDn(ByVal dbNum as Double) as Double
RndDn = (Int(dbNum * 100)) / 100
End FunctionI thought this was adequate until this morning when I encountered a line on the report that read:
142.266 * 10 = 1,422.65 ==> Should, obviously, = 1422.66
I discovered that I can get the correct result by re-writing the function as:
Code:
Public Function RndDn(ByVal dbNum as Double) as Double
Dim X as Double
X= dbNum * 100
RndDn = Int(X) / 100
End FunctionSo, I'm wondering if anybody can tell me why the two are different and, especially, why the first one is returning 1422.65?
Thanks
A/R
--I attached a simplified db to demonstrate.
