Decimal places in Dlookup missing

[lehcarrodan]

Hi I have a Dlookup in my vba code that goes to my tblTaxes and puts the number into a text box on my form (it works! Sort of..). Problem is, in the table it shows the proper value 9.975% and in the text box it cuts the last decimal. Why's this happening?/@^@&!!! Even when I tell it to show more it gives 9.9700000 how can I fix this?

Thank you for any help/suggestions

 

[arnelgp]

Do y have ant formatting on the textbox?

 

[Ranman256]

IF the field is set to decimal (single or double) it will hold a lot of decimals, tho you may only see a few.
format() is the answer to view more.

 

[lehcarrodan]

IF the field is set to decimal (single or double) it will hold a lot of decimals, tho you may only see a few.
format() is the answer to view more.

I have set the field to double..
It now seems to be rounding up the value 9.975 to 9.98 and then showing more decimals 9.98000

Really not getting this and seems pretty basic lol

I use this to look up table value:
Me.txtPST = DLookup("PST", "tblTaxes", "Province = '" & Me.txtBillState & "'")

Table stores the right value 9.975

 

[Pat Hartman]

Sorry but format() is NOT the answer to view more. is almost never the answer for anything. The Format() function turns a date or number into a string and that means the date/number will act like a string. Sorting will present this list - 1,10, 11, 12, 13, 2,20, 21, 22, 23 ,3,30, 4, 5, 6, 7 , etc which is unlikely to be what you had in mind. Same problem with the date. Logically, a date sort is year, month, day. However sorted as a string, if your format is month, day, year then all the january 1sts will come before all the January 2nds, etc rather than in human understandable order.
Using the Format() property in a query or ControlSource will render the field not updateable.

If you want to apply a format to a control or query column, use the format property. The only time I would ever use Format() in a query is if I were exporting the data to Excel and I wanted to control the date format or limit the number of decimal places for a number. This is necessary because the TansferSpreadsheet ignores the Format Property of the query columns so you have to force the issue.

Unless I need more than four decimal places, I always use the Currency data type. This limits the decimals to four and eliminates floating point rounding issues.

If you want a fixed number of decimal places and you do multiplication or division, you MUST round in the calculation so that only the desired number of decimal places is stored.

 

[GeorgeBenko]

I had the same issue. What I found if you use Dsum Instead of Dlookup it works. Why Dlookup doesn't work it is a mystery.

 

[gemma-the-husky]

it's a bit risky trying to test equality to a real number - ie with decimals, since many real numbers can't be completely represented in binary. Dlookup with a decimal number may fail to find a match because of tiny rounding errors.

 

[The_Doc_Man]

It now seems to be rounding up the value 9.975 to 9.98 and then showing more decimals 9.98000

This tells me that there are at least two steps here and one of the steps involves rounding. If you were working entirely in SINGLE datatype and just rounded to three places, you should get what you wanted. The fact that you didn't means that something else is in the picture that you haven't told us about, or that you have not realized.

Computers are predictable when it comes to math. (If they weren't we would have abandoned them decades ago.) SINGLE datatype math is capable of handling your problem. So ... where do you introduce something that ISN'T a SINGLE datatype?