Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding in a query (Access 97 SR2)

    I have a query which has a calculation in it. I'd like to round it up to the next number. So far I have found INT that knocks off the decimals and CINT that rounds to the nearest number i.e. o.4 rounds to 0 which is not what I want. I was thinking of adding 1 to the result using the INT command but that means all of the original round numbers will be rounded to the one about i.e. 1 will become 2 (think I explained that ok!).

    Does anyone have any ideas?

    Thanks in advance for any help you might provide.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Rounding in a query (Access 97 SR2)

    Try this:

    iif( yourNumber>int(yourNumber), int(yourNumber) + 1, yourNumber)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in a query (Access 97 SR2)

    how about

    iif(int(x)=x,x,x+1)

    In theory (assuming infinite precision) that should work. However, be careful that you don't run into problems with precision - you may need to use currency type to ensure that values that you think have no fractional part really don't

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in a query (Access 97 SR2)

    Thanks Mark, that worked perfectly.
    I haven't tried your version yet Douglas, but i'll give it a go when I get a minute.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding in a query (Access 97 SR2)

    If your number is always greater than or equal to zero you could use:

    RoundedUpNumber = -int(-YourNumber)

    It works for negative numbers, too, if you want the rounded number closer to zero (e.g., -1.1 rounds to -1 but not to -2). Note that for negative numbers, adding 1 as suggested by Mark and Douglas also gives you the rounded number closer to zero.

    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
  •