Round up value with SQL Access
donlefou
Posted messages
44
Status
Membre
-
buffon74 -
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.
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 réponses
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:
--
See you, Blux
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 1And there you go!
--
See you, Blux
"Fools dare to do anything. That's even how you recognize them"
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.
That's why int(nb+1) rounds up to the highest integer.
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
--
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
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.
that's why int(nb+1) rounds up to the highest integer.
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
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"
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
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 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.
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 :To see...
--
See you Blux
--
See you, Blux