Avatar of etech0
etech0
Flag for United States of America asked on

Access query round up

In an Access query, how can I round up a number, ie: to the next whole number?
Microsoft Access

Avatar of undefined
Last Comment
etech0

8/22/2022 - Mon
DoveTails

You can try the integer function ... INT and add one:

Expr1: Int([YourField])+1
Patrick Matthews

DoveTails,

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

Patrick
ASKER CERTIFIED SOLUTION
DoveTails

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

Try:

SELECT [SomeColumn], Int([SomeColumn]) + IIf(SomeColumn <> Int([SomeColumn]), 1, 0) AS RoundedUp
FROM SomeTable
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
kmslogic

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

Should do the trick for rounding to the nearest integer.
etech0

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!
kmslogic

@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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
etech0

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.