Use of the FIX vba function

[billelev]

I am trying to truncate double values in a query to 2 decimal places (i.e. round them down). As far as I am aware, there is not a "round down" function in vba, and so I have implemented the following method:

FIX( expression * 100 ) / 100

This, most of the time, works well. However, when I try the expression ( 5 * 85.41 ), which is equal to 427.05, i.e.

fix( 5 * 85.41 * 100 ) / 100

I get 427.04. If I try

Dim a As Double
a = 5 * 85.41 * 100
MsgBox (Fix(a) / 100)

I get 427.05, which is the correct value.

Something subtle is happening here which I'm not picking up on. Can anyone explain what is happening?

Thanks.

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

[Neekos]

I cant explain why that is happening, but im wondering why you are mulitiplying your expression by 100, only to divided by 100? you would get the same answer if you removed both.

[NeoPa]

I tried it out and I get the same as you.
I've thought of various answers but none of them seem to be right I'm afraid. I expect it's a rounding (internal calculation) issue, but why it should happen with one approach and not the other is beyond me for the moment.
Let's see what turns up. I may even have an idea myself if I leave it stewing for a while...

[NeoPa]

I cant explain why that is happening, but im wondering why you are mulitiplying your expression by 100, only to divided by 100? you would get the same answer if you removed both.

That's not right Neekos.
The value is multiplied by 100; then the Fix() function strips off any fractional part; THEN the result of that is divided by 100.

[Rabbit]

Here's my theory.

This works correctly:

Debug.Print Fix(CDbl(5 * 85.41 * 100))

What's happening is when the processor calculates the value as a double but is not assigning, i.e. converting, it to a double, it stores the number as the lowest amount of bits required. However, the Fix expression converts that back to a double.

Doubles don't have absolute precision. So when it gets calculated, it's something like 42705.000000000 0001. When stored to a double, that's what is stored. But instead it's passing 42705 at the lowest parity possible. When that gets converted to a double, it becomes 42704.999999999 9999999999.

That's what I think is happening anyways.

[billelev]

Here's my theory.

This works correctly:

Debug.Print Fix(CDbl(5 * 85.41 * 100))

Great! That is what is happening, I'm sure. However, if I try the following in a query field:

Value: Fix(CDbl([Quantity]*[AssetPrices].[Price]*[AssetPrices_1].[price]*100))/100

Where:

[Quantity] = 5
[AssetPrices].[Price] = 85.41
[AssetPrices_1].[price] = 1

CDbl does not seem to have an affect as the number returned is 427.04, not 427.05. Does CDbl not work in a query?

[Rabbit]

CDbl is a conversion function for VBA data types. Try creating a public function to do the calculation and call that function from SQL instead.

[billelev]

CDbl is a conversion function for VBA data types. Try creating a public function to do the calculation and call that function from SQL instead.

Yep, that was the option I went for. It slows down the query a bit but it works. I still had some trouble with using CDbl and so in the end I decided to add on a small amount to force the rounded value to have a decimal greater than 0. E.g.

Int(expression * 100 + 0.000001) / 100

Thanks for your help.

[Rabbit]

Not a problem, good luck.