Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following command if report code module

    [53PercentOfLine13ColB] = TotalsLine13ColumnB * 0.53

    TotalsLine13ColumnB contains 2,697.50

    I need [53PercentOfLine13ColB] to return 1,429.67

    [53PercentOfLine13ColB] returns 1,429.675 with properties Format blank, Decimal Places Auto

    [53PercentOfLine13ColB] returns 1,429.68 with properties Format Standard, Decimal Places Auto

    What do I have to change?

    Thanks, John

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Rounding rules in general say to round up if your last digit is 5 or greater, otherwise round down. That appears to be what Access is doing here - to round downward may require a custom rounding function as I believe the VBA function Round() also rounds .xx5 up if you specify two decimal places.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    check out http://www.mvps.org/access/modules/mdl0054.htm

    there is a redone rounding function as described below:

    As evident by the numerous threads on this topic in the different newsgroups, a lot of people have come across rounding errors in VBA when working with floating-point numbers. The primary reason behind such errors is that floating-point calculations with Single or Double data type often can't be represented in binary (due to large range of numbers these data types can operate on), leading to rounding inaccuracies.

    The workaround is to work with scaled (Fixed point) numbers, that is, Currency and Decimal data types. Here's a function that always works (using "banker's rounding") by converting the value passed to a Decimal data type.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    As Wendell indicates, what you want is not rounding, but truncating. You can use this expression to truncate the result to 2 decimals:

    [53PercentOfLine13ColB] = Int(TotalsLine13ColumnB * 53) / 100

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    A search on custom rounding function revealed Re: Rounding (Access 2000) <post#=367404>post 367404</post#>
    re: 367347 from cindypositano & Mark

    http://support.microsoft.com/default.aspx?...kb;EN-US;196652

    So I added the following and got correct rounding

    <pre>[53PercentOfLine13ColB] = SymDown(TotalsLine13ColumnB * 0.53, 100)

    Function SymDown(ByVal X As Double, _
    Optional ByVal Factor As Double = 1) As Double
    SymDown = Fix(X * Factor) / Factor
    ' Alternately:
    ' SymDown = AsymDown(Abs(X), Factor) * Sgn(X)
    End Function
    </pre>


    However I like Hans [53PercentOfLine13ColB] = Int(TotalsLine13ColumnB * 53) / 100
    solution better

    Thanks for the replys, John

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Need Help With Rounding? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Note that the truncation, as suggested by Hans does what you want for that specific number, but do you also want it to truncate (round down) if the result is 1,429.675999?
    Wendell

Posting Permissions

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