Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    12

    Unanswered: How to set number of Decimal Places?

    Ok I am apparently Access impaired....

    I have a query with a column that calculates a percentage based on the other columns in the query. I simply want to set the output of that column to a reasonable number of decimal places- say 2.

    All the forums and MS help say to set it in the "Decimal places" field on the properties menu. However- When I go there, I don't see that field.

    Alternatively, If I try to set the decimal placed in the field in the report version, it does nothing despite how I set the number, and there are no options under "format" (such as date, percentage, etc)

    What am I missing here?
    THANKS!!!

  2. #2
    Join Date
    Nov 2008
    Posts
    12
    Me again. For the mean time I embedded a ROUND expression into my percentage calculation. That gets my decimal places down, but it seems like a really silly way to go about it, so I'd still love some insight on the above issue so I can learn the right(?) way to do it! Thanks!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    When you format a number in tables or in forms/reports, you set the Decimal Places property to, say, 2. In queries, the best way to do it is the way you have found... by using the Round() function.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Acess has no concept of storing number of decimal places, unlike some db's you cannot actually set that in storage (assuming you use a single, double type), the closest you can get is using a currency type. You could frig it using a text field... but don't even think of going down that route...

    formatting alters the number of decimal places shown, but not the number of decimal places in the data. the problem of using formatting is that if you report or group footers then there is a strong possibility that the total will not the be the same as the sum of formatted values.

    That is why the round function is the way to go. its especially important on monetary values where the treatment of rounding up or down of numbers is an exciting and enthralloing topic for any accountant worthy of his grey suit,Volvo and golf clubs.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2008
    Posts
    12
    Well it sounds like using the Round function is the way to go. I still don't understand why setting the Decimal Places property as StarTrekker suggested dosn't work, but i think I'm past caring at this point.
    Thanks for the help guys!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    setting the number of decimal places should work at the table level, however forms and reports will only pick up that value when created from new, they will not pick up changes to the table design after the form or report is created
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    I would try:

    TwoDecimalPlacesinField = CLng([YourValue] * 100) / 100

    You get to a Long Integer and then divide by 100.

    Long Integer just in case Int is too small.

    Simon

Posting Permissions

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