Access query round up
In an Access query, how can I round up a number, ie: to the next whole number?
DoveTails,
If [YourField] already is at the integer point, your expression will increment it :)
Patrick
If [YourField] already is at the integer point, your expression will increment it :)
Patrick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try:
SELECT [SomeColumn], Int([SomeColumn]) + IIf(SomeColumn <> Int([SomeColumn]), 1, 0) AS RoundedUp
FROM SomeTable
SELECT [SomeColumn], Int([SomeColumn]) + IIf(SomeColumn <> Int([SomeColumn]), 1, 0) AS RoundedUp
FROM SomeTable
Expr1: int(nz([YourField], 0) + 0.5)
Should do the trick for rounding to the nearest integer.
Should do the trick for rounding to the nearest integer.
ASKER
Wow, you guys are fast!
DoveTails and peter57r - that did exactly what I need.
kmslogic: I tried that, but it rounded 1 up to 2.
Thanks to all of you for trying!
DoveTails and peter57r - that did exactly what I need.
kmslogic: I tried that, but it rounded 1 up to 2.
Thanks to all of you for trying!
@etech it sure shouldn't have...
Here's what I get when I run this in the VBA editor:
Debug.Print Int(1 + 0.5)
Debug.Print Int(1.4 + 0.5)
Debug.Print Int(1.6 + 0.5)
Output:
1
1
2
Now if you want to always round up no matter what the decimal is (like always round 1.01 to 2) then the - int(- Field)) solution is right. If you want to round values less than X.5 (e.g. 2.4, 1.01, 27.315) DOWN and all greater than or equal to X.5 (e.g. 12.5, 13.9, 100.8) UP then you should use what I posted.
Here's what I get when I run this in the VBA editor:
Debug.Print Int(1 + 0.5)
Debug.Print Int(1.4 + 0.5)
Debug.Print Int(1.6 + 0.5)
Output:
1
1
2
Now if you want to always round up no matter what the decimal is (like always round 1.01 to 2) then the - int(- Field)) solution is right. If you want to round values less than X.5 (e.g. 2.4, 1.01, 27.315) DOWN and all greater than or equal to X.5 (e.g. 12.5, 13.9, 100.8) UP then you should use what I posted.
ASKER
@kmslogic: I want it to always round UP.
Actually what I had tried is something similar to yours - Round([int]+.5), which also rounded 1 up to 2.
- int(- [Field]) did exactly what I need it to.
But thanks for your efforts.
Actually what I had tried is something similar to yours - Round([int]+.5), which also rounded 1 up to 2.
- int(- [Field]) did exactly what I need it to.
But thanks for your efforts.
Expr1: Int([YourField])+1