View original (French)

Round up value with SQL Access

donlefou Posted messages 44 Status Member -  
 buffon74 -
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 Posted messages 5040 Registration date   Status Moderator Last intervention   3 455
     
    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 Posted messages 44 Status Member
       
      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 Posted messages 5040 Registration date   Status Moderator Last intervention   3 455
       
      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
      1. donlefou Posted messages 44 Status Member > blux Posted messages 5040 Registration date   Status Moderator Last intervention  
         
        Alright, that's good, it works, thanks Blux. See you soon!
        0
      2. blux Posted messages 5040 Registration date   Status Moderator Last intervention   3 455 > blux Posted messages 5040 Registration date   Status Moderator Last intervention  
         
        I'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 Posted messages 1612 Status Member 155
     
    and 2 wouldn't that make 3 with this method?
    --
    isn't life beautiful?
    1
  3. 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
  4. _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
  6. 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
  7. aquaman
     
    false: int(1+1) = 2
    while rounding 1 gives us 1

    not yet the right solution
    0
    1. blux Posted messages 5040 Registration date   Status Moderator Last intervention   3 455
       
      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
      1. b747fred > blux Posted messages 5040 Registration date   Status Moderator Last intervention  
         
        For 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
      2. buffon74 > b747fred
         
        I 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