Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    6

    Unanswered: Decimal place display

    I have inherited a slightly modified inventory control db. My goal is to have the 'Unit Price' category fill in automatically once a product has been selected in the purchase order form. I currently have this working by using a query and event procedure but I need 5 decimal places which rules out currency. I changed the 'Unit Price' data type to number, field size to single and format to standard. I have changed these also in the purchase order subform so they match and there are no references to currency as was the default. After doing so it is still rounding to four digits. I am a bit confused as to why. I am able to enter 7 digits in the product table under 'Unit Price' but it is not displayed similarly in my purchase order form. I have also tried formats of general number and undefined.
    Last edited by Matt Apel; 05-15-07 at 23:58.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check the format of the control in the relevant form. if the form was designed at the time the column in the table was formatted as currency then the form will have inherited the currency format mask. Access doesn't ripple changes made to data types, formats or validation rules in the table design through existing forms and reports.

    if you are entering data with more than 4 decimal places then that will be how the data is stored, but it weill almost certainly be displayed as previously specified.

    Id also check the number of decimal places on the controls properties tab
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...aside: forget about formatting table fields - it's a waste of time: if you don't let users see tables you have no need for this pointless distraction. all your tables need is an appropriate datatype.

    all form fields have a 'Decimal Places' property: set to 5 and you should be done.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    May 2007
    Posts
    38
    if its a price, use currency type, and set dps to correct number. I am sure currency is most reliable number format for more significant dps.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    1,000,000% Husky
    unfortunately OP believes that 5 places are needed: decimal datatype will handle that but adds absurd burdens.
    izy
    currently using SS 2008R2

  6. #6
    Join Date
    May 2007
    Posts
    6
    The lack of a ripple effect explains why the display wouldn't change despite my experimenting with the formats after altering the data type.The format was currency in the relevant form as was the data type in the table. Is there a way to reset or alter the data type that the form was defined with?

    Five decimal places are necessary because many of the products that I purchase are sold $x.xx/1000. So if the cost was $123.45/1000 then the unit price is .12345. Hence, the need for five decimal places. This unit price is what I am wanting to display accurately for the finance department.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are going to have to change every pre-existing form and every report that uses that column and select an appropriate format and or decimal places.

    if you haven't used an input mask then the data will exist in whatever number of decimal places.

    bear in mind there is nothing to stop you creating an appropriate format mask say "£#,##0.00000".
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2007
    Posts
    38
    I am sure the currency format is the most accurate to significant numbers of dps - but check help to make sure. Even so, I am sure double is also accurate to at least 5dps, and it is not the data storage format giving you your problems

    the problem is that the display format may APPARENTLY round the display (for the display purposes only) but the data remains intact and will be used in its full form in calculations which may result in some unexpected diffrerences/side-effects.

    If you want to see 5dps then you need to set the field display to show that number of dps.

    you can check this by using the apparently rounded value in further calcs to see that the NUMBER itself is not changing

    eg 123.45 divided by 1000 is 0.12345
    this may display as 0.12

    however if you multiply the displayed field by 1000 again, I am sure you will get back to 123.45 and not 120.00 (wehich you would do if the display format affected the underlying data)

  9. #9
    Join Date
    May 2007
    Posts
    6
    After playing around with it using the advice given I noticed the control source for the form was not the product table but was the transaction table which also used the field 'Unit Price'. After changing its format from currency, all was well. The custom formatting worked equally well. This small excursion was useful in understanding access a bit more. Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •