Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Proper use of Round function

    Using Access 2007

    On a form, I have a text box that pulls the total of Materials ordered from a subform fsubMaterials. The Materials are then marked up by the contractor, with a default of 20% but the contractor can change this value in a text box called txtMarkup.

    Is this the proper way to do that Round function?
    Code:
    =Round([txtMarkup]*Nz([fsubMaterials].[Form]![txtMaterials],2),2)
    The value is then added to the total Materials cost to produce a TotalMaterialsCost, which has the following construction.
    Code:
    =Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+[txtMarkupAmt]
    Perhaps there is a way to do this in one step and I haven't played with that yet. My main concern is getting the Round properly.

    Tom

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    The round function has 2 arguments
    1. Value to round {can be a formula}
    2. Number of decimal places to round

    So the proper format is =Round(something that results in a number, number of places to round)

    You say the markup can be changed but my question is can the change the percentage or the amount?
    If they are changing the percentage you don't want to add that amount.

    Note: if number of places to round is negative it rounds to the left, i.e. -2 would round to the nearest 100.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG
    Thanks for the input.

    To clarify, here are the steps.
    1. The user input the varied Materials in fsubMaterials
    2. The text box in the footer of fsubMaterials sums the cost of those Materials. That text box is called txtMaterials
    3. On fsubProjects, the txtMarkup text box has a default of 20% but that percentage can be modified by the user.
    4. On fsubProjects, a hidden text box called txtMarkupAmt calculates the amount of the Markup as follows:
    Code:
    =Round([txtMarkup]*Nz([fsubMaterials].[Form]![txtMaterials],2),2)
    5. On fsubProjects, another text box called TotalMaterialsCost takes the value from txtMarkupAmt and adds it to the value from txtMaterials, as follows:
    Code:
    =Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+[txtMarkupAmt]
    Tom

  4. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    You could have avoided using the hidden text box by using
    =Nz(Round([fsubMaterials].[Form]![txtMaterials],2),0)+ (1 + [txtMarkupAmt])

    BTW, are you aware that if [fsubMaterials].[Form]![txtMaterials] has a null value, then Nz([fsubMaterials].[Form]![txtMaterials],2) will result in a value of 2?

    Cronk

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Cronk. Yes, I see what you mean by the misplacement of the "2"

    Tom

Posting Permissions

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