DECIMAL PLACES IN MS ACCESS TABLES

cyrusd
cyrusd used Ask the Experts™
on
I have a PRICE filed in my TABLE and it is a NUMBER field. I have set the DECIMAL PLACES to 2 Places. Everytime i enter any number like 3.6 it gets rounded to 4.00 and 2.3 gets rounded to 2.00

How do I not round off the number ? Also if i correct it problem in the table wil i have to corrct it in all the forms where the field is part of ?

Thanks !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Cyrusd,

The field you're talking about is most likely an integer type. Change numbertype to single or double in the tabledefinition . The good news is you don't have to make changes on your form.

Author

Commented:
Hi zuijdhoek,

Thanks for your promt help. Just before i saw your solution, i set in from INTEGER to DEIMAL and in the SCALE i put that to 2 from its original 0.

I also tried your solution and it works. What shouodl it be ideally ? what is teh difference between the DEMILA and SCALE solution and using DOUBLE !

CYRUS
For accuracy you should be using a Currency field, this is the most accurate for calculations, change the Data Type From Number to Currency.

Cheers, Andrew
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Cyrus,

The scale-property can be set in order to determine the amount of decimal digits to be displayed.
Concerning singles an doubles: you have to consider what values will be stored in this field. A single uses 4 bytes for each decimal with a decimal precision of 7 digits. A double is even more precise (up to 15 decimal digits) but uses 8 bytes. A single type will do in most situations.  
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Cyrus,

<<I also tried your solution and it works. What shouodl it be ideally ? what is teh difference between the DEMILA and SCALE solution and using DOUBLE !>>

  The difference is accuracy as TextReport said.  Due to the nature of floating point math, decimals can be rounded.  To avoid this, Access uses scaled integers, that is a number is multipled by a base of 10^x where 'x' is the number of decimals.

  So if you want two decimals,  12.34, you would need to use the decimal data type with a scale of two. When your number is stored, it is first multiplied by 10^2 (100), and you end up with 1234.

  When you go to work with the number again, it is divided by 100 automatically.

  Before A2K, the currency data type was the only scaled integer data type available (its scaling factor is 4).  The decimal data type was added after that and is more flexable as you can specify the scaling factor.

HTH,
Jim.

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
cyrusd,

Questions Asked
27
Last 10 Grades Given
B A A B B
Question Grading Record
5 Answers Graded / 5 Answers Received

Administration will be contacting you shortly regarding your account.

SpideyMod
Community Support Moderator @Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial