View original (French)

Round up value with SQL Access

donlefou -  
Hello, I would like to know if in an SQL query it is possible to round a value up to its next higher integer.
There is the Int(Number) function which only rounds to the nearest integer (0.3 becomes 0).
I would like to round 0.3 up to 1.

6 answers

  1.  blux Moderator
    Hi,

    The ROUND function does not exist in ACCESS...

    But we can work around it. We need to check if the number is a decimal (so we look at the result of the number minus its integer part). In that case, we isolate the integer part and add one to it; that's the purpose of the query below: 
    SELECT TABTEST.[champ], IIf([champ]-Int([champ])=0,[champ],int([champ])+1) AS Resu FROM TABTEST; champ Resu 0.024 1 0.5 1 1 1 14.5 15 2.3 3 0.1 1 0.3 1 1 1 2 2 3 3 1245 1245 45 45 1256 1256 4584.256 4585 2 2 6 6 7 7 8 8 95 95 0.0003 1
    And there you go!

    --
    See you, Blux 
     "Fools dare to do anything. That's even how you recognize them"
    2
    1. donlefou Author
      Thank you Blux, but it seems to me that there is a small problem.
      0.2 gives 1 OK
      1.4 gives 2 OK
      but 1.6 gives 3 when it should give 2
      I think there is a small adjustment to be made in the formula.
      0
    2. blux Moderator
      Re-tested at my place, it's working well...

      Problem with int? with the calculation of the difference? Fields described in reals?
      What version of Access? (97 at my place...)

      try to display only the result of the difference :
       SELECT TABTEST.[champ], ([champ]-Int([champ])=0) AS Resu FROM TABTEST;
      To see...

      --
      See you Blux 
       "Fools dare to do anything. That's even how you recognize them"
      0
    3. donlefou Author
      @bluxAlright, that's good, it works, thanks Blux. See you soon!
      0
    4. blux Moderator
      @bluxI'm interested to know where the mistake came from...

      --
      See you, Blux 
       "Those fools dare anything. That's how you recognize them"
      0
  2. random
    and 2 wouldn't that make 3 with this method?
    --
    isn't life beautiful?
    1
    1. no_pain
      In fact, the int() function does not return the closest number but the lower integer (0.3 ==> 0 but 0.8 ==> 0)

      That's why int(nb+1) rounds up to the highest integer.
      1
      1. _Agnes
        Hi

        I believe that the ROUND function rounds to the nearest integer. What you can do is round and then add +1 to the rounded value...

        See you
        -- 
        Lost time can never be regained. So let's continue doing nothing. J.Renard
        0
        2. no_pain
          In fact, the int() function does not return the closest number but the lower integer (0.3 ==> 0 but 0.8 ==> 0)

          that's why int(nb+1) rounds up to the highest integer.
          0
          1. aquaman
            false: int(1+1) = 2
            while rounding 1 gives us 1

            not yet the right solution
            0
            1. blux Moderator
              The initial question was to round up to the nearest whole number, the formula I provided at the time works perfectly.

              1 rounded gives us 1 The rounding function does not exist in ACCESS...

              --

              A+ Blux 
               "Idiots dare to do anything. That's how you recognize them"
              0
            2. b747fred
              @bluxFor your information, if it can help someone

              Function for rounding up in all cases in the expression of an Access query

              x is a real number

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

              x=1

              int(1) is equal to 1, so it gives 1

              x=1.1

              int(1.1) is not equal to 1, so it gives 1+1=2

              Of course, you can replace x with the value of a field, but in this case, don't forget the brackets

              x is a field

              iif(int([x])=[x];[x];int([x]+1))

              Best regards

              b747fred
              0
            3. buffon74
              @b747fredI was searching the Internet for how to round up a real number in Access. I came across your message which helped me greatly.
              Thank you.
              0