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

 

[Pat Hartman]

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().

That should be the BeforeUpdate event. The AfterUpdate event fires AFTER the record is saved so it is too late to stop the error.

In your table design set the Format to Fixed and Decimal Places to 3.

Setting the format doesn't have anything to do with what gets saved. If you want to control the number of decimal positions saved, you can do it in the Form's BeforeUpdate event by rounding the value to three decimal places.

If you never need more than four significant decimal digits, you can avoid floating point errors by using the Currency data type - not to be confused with the Currency format. The Currency data type is a scaled integer. That means that it is an integer with an implied decimal position. 44.3 would be stored as 443000. The decimal position is ALWAYS fixed between pos 4 and 5 from the right.

 

[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.