Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    26

    Unanswered: Decimal Places / Form Calculation Field

    A follow on to my previous post: http://www.dbforums.com/microsoft-ac...ml#post6408129

    I used a text box in a form to create a calculated value based on two fields in the form: sales and cost to yield profit. I would like profit to appear with two decimal places all the time -> for example 5.55 or 6.00. So that you know, I have set Format: to General Number and Decimal places to 2. The result however is either 6 with no decimal places or 5.230498230498203.

    Thank you,
    David

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could try using the CCur() function in your calculations:

    In a Query: Profit: CCur([Sales] - [Costs]) in the Field Row of a new column.
    In a Form/Report: =CCur([Sales] - [Costs]) in the Control Source of a Textbox.

    Failing that, use the Format([Sales] - [Costs],"#,##0.00") 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

  3. #3
    Join Date
    Apr 2009
    Posts
    26
    Quote Originally Posted by StarTrekker
    You could try using the CCur() function in your calculations:

    In a Query: Profit: CCur([Sales] - [Costs]) in the Field Row of a new column.
    In a Form/Report: =CCur([Sales] - [Costs]) in the Control Source of a Textbox.

    Failing that, use the Format([Sales] - [Costs],"#,##0.00") function.
    StarTrekker,

    Thank you for you help. The first option did not work, but the second did the trick! In trying to understand the formatting of the Format() function, I did some research and found a link which might be helpful to some.

    MS Access Format Function

    Thanks Again,
    Dave

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome

    Sometimes I find CCur() just doesn't work for me
    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

  5. #5
    Join Date
    Apr 2009
    Posts
    26
    Quote Originally Posted by StarTrekker
    You're welcome

    Sometimes I find CCur() just doesn't work for me

    Hi, one more quick question. The calculated fields show "#Div/0!" before values are entered into the revenue and cost lines. I have tried entering "null" into the default value under properties, but no luck. Is there a way to have the calculated field look more appealing / less confusing prior to data entry?

    Thank You
    David

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, encapsulate the expression in an IIF function:

    =IIf([Cost] > 0, <currentformula>, "")

    That kind of thing
    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

  7. #7
    Join Date
    Apr 2009
    Posts
    26
    Quote Originally Posted by StarTrekker
    Yes, encapsulate the expression in an IIF function:

    =IIf([Cost] > 0, <currentformula>, "")

    That kind of thing

    Thank you again StarTrekker.

    Thanks,
    Dave

Posting Permissions

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