Access query round up

etech0
etech0 used Ask the Experts™
on
In an Access query, how can I round up a number, ie: to the next whole number?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can try the integer function ... INT and add one:

Expr1: Int([YourField])+1
Top Expert 2010

Commented:
DoveTails,

If [YourField] already is at the integer point, your expression will increment it :)

Patrick
YIKES...good point:

Try ;   xpr2: -Int(-[Field1])
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Use
- Int( - [yourField])
Top Expert 2010

Commented:
Try:

SELECT [SomeColumn], Int([SomeColumn]) + IIf(SomeColumn <> Int([SomeColumn]), 1, 0) AS RoundedUp
FROM SomeTable

Commented:
Expr1: int(nz([YourField], 0) + 0.5)

Should do the trick for rounding to the nearest integer.

Author

Commented:
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!

Commented:
@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.

Author

Commented:
@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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial