Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Rounding a number up function in a report

    Hi all,

    I'm attempting to round numbers up in a report. I found a function in Microsoft Knowledge Base Article No. 209996 (http://support.microsoft.com/?kbid=209996) called RoundToNearest. It runs fine in the Debug Window, but won't in the report.
    In the report, I'm entering it as...

    =RoundToNearest([Weight], 0.1, up)

    However, after I save the report, it is changed to...

    =RoundToNearest([Weight], 0.1, [up])

    ...which causes it to ask for a parameter value of "up" when the report is opened.
    Am I not calling the function correctly?

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    hm, first, try using only a number, say - 1 - instead of "up", as it in the referred function is a variant, and it only tests for ANY existence, ie. if that argument/variable is empty, it rounds down, otherwise up. The Up word itself is irrelevant. The function rounds up if there is ANY value sent in that "place" of the function.

    BUT: in your case: if all you need is to round UP, change the following

    FROM:

    Code:
          If IsMissing(varUp) Then
             ' round down
             dblTemp = lngTemp
          Else
             ' round up
             dblTemp = lngTemp + 1
          End If
    TO
    Code:
          If IsMissing(varUp) Then
             ' round up
             dblTemp = lngTemp + 1
          Else
             ' round down
             dblTemp = lngTemp
          End If
    This way, you do NOT have to include anything , as long as you do NOT pass on the actual variant/variable, it rounds UP as default instead of down.

    D.

  3. #3
    Join Date
    Jun 2004
    Posts
    2
    Thank you very much for your suggestion.
    I made the change (to round up if the variant is missing), which I think solved one problem. Using "=RoundToNearest([Weight],0.1)" in the report, though, here's what happened...

    Weight Rounded
    0.01 0.1
    0.02 0.1
    0.03 0.1
    0.04 0.1
    0.05 0.1
    0.06 0.2
    0.07 0.2
    0.08 0.2
    0.09 0.2
    0.1 0.1
    0.11 0.2
    0.12 0.2
    0.13 0.2
    0.14 0.2
    0.15 0.2
    0.16 0.3
    0.17 0.3
    0.18 0.3
    0.19 0.3
    0.2 0.2
    0.21 0.3
    0.22 0.3
    0.23 0.3
    0.24 0.3
    0.25 0.3
    0.26 0.4
    0.27 0.4
    0.28 0.4
    0.29 0.4
    0.3 0.4
    0.31 0.4
    0.32 0.4
    0.33 0.4
    0.34 0.4
    0.35 0.4
    0.36 0.5
    0.37 0.5
    0.38 0.5
    0.39 0.5
    0.4 0.4
    0.41 0.5
    0.42 0.5
    0.43 0.5
    0.44 0.5
    0.45 0.5
    0.46 0.6
    0.47 0.6
    0.48 0.6
    0.49 0.6
    0.5 0.5

    As you can see, 0.06 through 0.09 rounded up to 0.2 instead of 0.1; 0.16 through 0.19 rounded up to 0.3 instead of 0.2; etc.
    Any thoughts why this may be occurring?
    By the way, I leave for vacation tomorrow, so I won't be able to respond for a week. But thanks again for your help!

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    hm, yes, I wasn't really studying the rest of the calculation in the function very well, just pointed out that it could be done in the opposite way.

    I guess the calculation of the function must be adjusted to fit "the other way around".

    Will see if I can have a look at it later, no time now, perhaps another person can fix that presumably small part and post a new code example here.

    D.

Posting Permissions

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