Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

What's best way to Round UP to next integer in MS Access 2000 in a query?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ROUND

Rounds a number to a specified number of digits.

Syntax

ROUND(number,num_digits)

Number is the number you want to round.

Num_digits is the number of digits you want to round to. Negative rounds to the left of the decimal point; 0 (zero) rounds to the nearest integer.

==========================

If that doesn't work try adding .5 to the number then use the INT function: INT(number + .5)

This is an old programming trick :)

SELECT ROUND( <value> +.5, 0) AS Number

where <value> is the value or variable you want to round

First of all, it's not supported in Access 97 - I don't know if it is in A2K, but it's definitely in A2K2.

Secondly, like CLng, it rounds to the nearest EVEN number. Therefore, Round(1.5,0) and Round(2.5,0) both return 2. You cannot simply add 0.5 to the number to round. Consider what happens if <value> is exactly 1:

Round(1+0.5,0) = 2

Therefore you must apply a check to see if the value is already an integer amount before you perform the rounding.

1) ROUND does work in Access 2000

2) The function operates correctly by itself... no need the +.5

Good Luck

Option Compare Database

Option Explicit

Function RoundUpMod(ByVal Number As Currency) As Currency

Dim P As Integer

Dim N As Currency

If Number < 0 Then P = -1 Else P = 1

N = Abs(Number)

N = N * 100

N = Int(N)

While Right(N, 2) <> 0

N = N + 1

Wend

N = N / 100

N = N * P

RoundUpMod = N

End Function

SELECT CLng( number + .5) as Expr;

This statement works in Access 2000 and will round up any number to the next whole number. For example:

2.00001 -> 3

2.9 -> 3

2.0 -> 2

-guggs

CLng(3.5)=4

CLng(4.5)=4

CLng(5.5)=6

CLng(6.5)=6

I will leave a recommendation in the Cleanup topic area that this question is:

Delete question, no refund

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William

EE Cleanup Volunteer

Option Compare Database

Option Explicit

Function RoundUpMod(ByVal Number As Currency) As Currency

Dim P As Integer

Dim N As Currency

If Number < 0 Then P = -1 Else P = 1

N = Abs(Number)

N = N * 100

N = Int(N)

While Right(N, 2) <> 0

N = N + 1

Wend

N = N / 100

N = N * P

RoundUpMod = N

End Function

The question is too ambiguous to determine if shane's function will work.

If the intention is that any fraction rounds up (to include say .1) then Shane's function should work. If the intention is past .5 it will round up more frequently half the time.

Also, negative numbers I believe are a challenge with shane's code as well.

SpideyMod

Community Support Moderator @Experts Exchange

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialAs you may know, PAQ/No Refund is the WORST option: If the question has enough information to be called an answer, then someone's comments should be selected; if not, then the question should be deleted.

But in this case, it actually fits well. The questioner never replies and there was a decent amount of debate as to what would work and what wouldn't. It may have some good information for the PAQ, but not enough to award.

However:

>>> >>> I think my original answer would have worked :)

that sounds as if you never tested your original answer... if you could prove that your solution works, then that's a different story... ;-)

Andrew

I am aware that PAQ/No Refund is one of the least favoured options.

As to my answer not being tested - it *was* tested. It is a formula I use in a number of applications, and it works PROVIDED that the requirement is to round a positive number UP to the next integer regardless of the decimal part.

There was not sufficient indication that this was indeed the questioner's requirement, hence my comment that I agree with Spidey's decision.

Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

IIf(Fix([MyField])<>[MyFie