Dlookup returning unnecessary decimal places

[matt753]

I'm using the Dlookup function to return the value from a table. The value is "0.05", in the table the fields format is number and decimal places set to 2.

When I run the dlookup, it returns "0.050000000745 0581". Where does it get this number from and why does it add on extra decimal places?

Even if I put Round(Dlookup(" ","",""),2) it still displays all those decimal places.

[NeoPa]

Where are you storing the returned value (what type of variable)? and what are you returning from the table (Field? Calculation? etc)?

It would be a help I'm sure, if you could post the actual line of code used.

[matt753]

Heres the code:

CTaxRate = DLookup("[RateValue]", "[Rates]", "[Rate] = '" & CRegion & "'")

The returned value is going into a textbox. It is a double in the backend table, specified in the table design to only have two decimal places. When I inputted the value I only typed "0.12".

[NeoPa]

Still a bit confused.

Is CTaxRate a TextBox control on a form?

Did the value 0.0500000007450 581 result from typing in 0.12?

[matt753]

The CTaxRate is a double variable, then it goes into a textbox.

The 0.0500000007450 581 is an example of what shows up up 0.05 was entered. it will be a number very very close to the two decimal version just with a lot of unnecessary numbers added on.

I think it might be storing it in the table this way for some reason. Even though it is set to 2 decimal places in the table design.

[NeoPa]

matt753: The CTaxRate is a double variable, then it goes into a textbox.

So we're not talking about a field in a table then?

matt753: The 0.0500000007450 581 is an example of what shows up up 0.05 was entered. it will be a number very very close to the two decimal version just with a lot of unnecessary numbers added on.

Double variables do not store numbers to the base 10, so they will mostly be just a very accurate representation in binary. This is not unusual.

matt753: I think it might be storing it in the table this way for some reason. Even though it is set to 2 decimal places in the table design.

I thought we'd already decided this was not table related. Never mind. Assuming for now it's not a variable and is a field, then all you've selected with the 2 decimal places is how it is formatted when shown. This is not related to how it's stored.

[matt753]

Sorry I got a bit confused with some of the posts back and forth here we each thought we were talking about different things.

CTaxRate = DLookup("[RateValue]", "[Rates]", "[Rate] = '" & CRegion & "'")

The Dlookup returns the "0.05" or "0.12" from the 'Rates' table, and puts it into a double variable. The variable 'CTaxRate' is then put into a textbox.

[NeoPa]

OK. So you now have a Double variable, CTaxRate, which has the value 0.05 assigned to it. Being a binary based exponentially stored variable, this value cannot be contained exactly. It therefore holds the closest value it can manage (0.050000000745 0581). This value is later assigned to your TextBox control. If there is no format designed into this control it will attempt to show this value (Not the 0.05 from the table) as accurately as possible. If it is long enough it will show it all. If too short it would round it where necessary to fit. Even if rounded for display purposes, the value held would still match the approximate value from the Double variable.

Does this explain all you need to know?

[matt753]

Oh ok this makes sense now, and I guess i shouldnt be too worried anyway, that decimal would have to be multiplied by a very large currency amount to even make a $0.01 difference.

Thanks for the help and explanation

[NeoPa]

That's true.

Also though, there are other methods that allow storage in Decimal, Currency etc.