Odd Behaviour of Int() in Function

[AJR]

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:

Public Function RndDn(ByVal  dbNum as Double) as Double
  RndDn = (Int(dbNum * 100)) / 100
End Function

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:

Public Function RndDn(ByVal dbNum as Double) as Double
   Dim X as Double
   X= dbNum * 100
   RndDn = Int(X) / 100
End Function

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.

 

[sneuberg]

That does seem strange that just assigning it to a variable would make a difference. I played with int in the immediate window and I don't see a pattern, but I guess it has something to do with decimal to binary conversions. Here's what I was seeing

?Int(142.60 * 10000000)
1425999999
?Int(142.61 * 10000000)
1426100000
?Int(142.62 * 10000000)
1426200000
?Int(142.63 * 10000000)
1426299999
?Int(142.64 * 10000000)
1426399999
?Int(142.65 * 10000000)
1426500000
?Int(142.66 * 10000000)
1426599999
?Int(142.67 * 10000000)
1426699999
?Int(142.68 * 10000000)
1426800000
?Int(142.69 * 10000000)
1426899999

 

[gemma-the-husky]

currency doesn't always display the currency symbol. it may do in the table, but that shouldn't matter.

if you want accuracy to 4dps, I would use currency.

 

[sneuberg]

If you change the type to currency in the table both functions give the same result (1422.66).

 

[AJR]

Funny how often problems stand on assumptions. I said that Currency type always displays the $ symbol because it does. In a report or form where the control is formatted as Currency. It never occurred to me that I could format the table field as Currency and the report control as something else. Which I can, did, and, yes, it does solve the problem. Thanks to both of you for taking the time. Still curious as to the why though.

AJR

 

[Rabbie]

IIRC this sort of problem is due to conversion from Real to Integer. REal is stored as a floating point number and this can lead to small inaccuracies/rounding errors when it is displayed.