How to allow 3 decimals in the table, query, and form.

[Khurramshaikh47]

Hi,
I have a number field in my table. I want to display 3 decimals like 3569.150 but when I type "3569.150" access automatically converts it to "3569.000". How can I allow it to show the numbers after decimal points?

Waiting for the expert's guidance,

Thank you

 

[ebs17]

You should use a data type for decimal numbers for the field, i.e. one that can also manage decimal parts.
Not Long, Int, but Currency, Decimal, Double

 

[561414]

Open the table in design mode and change the data type to the appropriate type, double for your case or currency if you will need precise calculations

 

[Cotswold]

In your table design set the Format to Fixed and Decimal Places to 3.
Also on your entry form go to Format Properties and again set Format to Fixed and decimal places to 3

You may also want to add a check in your AfterUpdate event to ensure that a user doesn't leave a Null in the field using Vartype(). If a user is in the field and presses [Delete] or a [Space] Key then you will not have a number in the field which will cause calculation issues like divide by zero etc..

See VarType() for details of use. Do a check similar to If Vartype([Field]) <3 or Vartype([Field]) >5 Then [Field] = 0 in order to avoid errors in calcs later because the Null entry is converted to Zero. If a zero entry is invalid then check for that as well.

 

[CJ_London]

you might also want additional checks to see if a user has entered more than 3 decimal places, since the format property will hide that and unless you use something like the round function in calculations you could get small differences

 

[The_Doc_Man]

When using DOUBLE data type, there is also the potential for some oddball rounding. (Not certainty, but potential.) IF you are sure you will never need more than 4 decimal places (you DID say you only wanted 3), use the Currency data type. It will have plenty of range and will be precise to that level.

Just to clarify: The rounding for DOUBLE occurs because 1/10, 1/100, and 1/1000 do not come out even in DOUBLE fractions. That's because each 1/10 is actually based on factors 1/2 and 1/5. The 1/2 is exact in binary fractions - but 1/5 is not. It is a repeating pattern. Therefore my suggestion is to avoid DOUBLE when you meant CURRENCY.

Your original complain LOOKS like you may have set a "NUMBER" data type but forgot to look at the field attributes on the bottom of the field attributes section to see that you selected either WORD INTEGER or LONG INTEGER. So... setting decimal places to 3 still works - and gives you ".000" at the end of each integer.

 

[CJ_London]

WORD INTEGER?

 

[The_Doc_Man]

OK, Access data type INTEGER, which IS a WORD in memory allocation and an INTEGER in math operation. Just like Y/N is a BYTE INTEGER. I come from a different background than pure Access so for me, some old habits are hard to break. Sheesh, give an old guy some slack, whydontcha?

 

[CJ_London]

you got it

 

[Cotswold]

Completing what is usually referred to as Integer calcs is the only way to assure 100% accuracy of results.

Personally, if I was doing any critical calcs in my software I would do the calcs, then convert it to a Long Integer and save the result in the table in a Long Integer Field. Then when the said field is displayed onscreen or in a report I simply string handle the decimal point for that field on the fly. Then total the fields and string handle that totalled result.

Also, if required you can build in user set, or programmed set rounding, before the Long Integer is arrived at. Even over many thousands of lines the resulting total will never be in error by even one 1000th (in this case). You never need to check integer calculations in your software.

TIP! Create a global Function that accepts any variable type and the number of decimal places required as parameters that returns the correct decimalised result. If something passed is invalid then it should return a decimalised zero. Then put that function into your Queries with the field in question and the number of decimal places required as the two parameters. Or alternatively directly onscreen, or on the reports. Q.E.D.

 

[gemma-the-husky]

You need to use a fixed format such as currency that will explicitly store 3 dps of accuracy. The underlying problem is that some numbers cannot be expressed with 3dps (such as 1/3), and you have to decide how to deal with that situation.