Round UP to next integer in MS Access 2000 in a query?

What's best way to Round UP to next integer in MS Access 2000 in a query?
KButler6Asked:
Who is Participating?
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.

shanesuebsahakarnCommented:
Generally, I would use a function like this:
IIf(Fix([MyField])<>[MyField],Fix([MyField]+1),Fix([MyField]))
0
pmeloniCommented:
You can use the function INT(yournumber) that made exactly for that.

Hope that help
0
shanesuebsahakarnCommented:
Int only chops off the decimal part of the number - it doesn't perform any rounding.
0
Ultimate Tool Kit for Technology Solution Provider

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.

guggsCommented:
From Access Help file:

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 :)
0
nexusnationCommented:
=Round([YourField],0)

and nice trick, guggs!
0
guggsCommented:
Sorry not INT, but ROUND function:

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

where <value> is the value or variable you want to round
0
shanesuebsahakarnCommented:
Two points about using the Round function:

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.
0
guggsCommented:
True.. a few tests shows:
1) ROUND does work in Access 2000
2) The function operates correctly by itself... no need the +.5
0
shanesuebsahakarnCommented:
That's true, but the questioner was asking to always round up - I assumed this meant that if the number had any decimal part at all, it was to be rounded up to the next integer (as opposed to mathematical rounding). This isn't uncommon in situations like pricing and so on.
0
EmilGCommented:
Here is a function that rounds up a number so that 234.34 would become 235.00. It also preserves if the number is positive or negative. If that is what you are trying to do you can use the following.

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
0
guggsCommented:
Okay sorry for all the bad answers.  Here's your answer tested and working:

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
0
shanesuebsahakarnCommented:
Umm, but 1.0 will still round to 2.
0
nexusnationCommented:
are you sure? works for me...
0
shanesuebsahakarnCommented:
Yep, pretty certain. In the immediate window, type:
?CLng(1.5)

1.5 being 1+0.5. You should get 2.
0
nexusnationCommented:
then why does 2.0 ...

2.0 + .5 = 2.5

round to 2? or is guggs wrong in his post on 2/27/03 12:36PM
0
shanesuebsahakarnCommented:
Nope, guggs is correct, but when the decimal part is exactly 0.5, CLng rounds to the nearest even number, ie:
CLng(3.5)=4
CLng(4.5)=4
CLng(5.5)=6
CLng(6.5)=6
0
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
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
0
shanesuebsahakarnCommented:
I think my original answer would have worked :)
0
EmilGCommented:
If there are only 2 decimal places my answer would work as well. If the number of decimal places vary then you would have to test for the number of decimal places and change the multiplier/divisor programatically for each number. :)

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
0
SpideyModCommented:
PAQ NO Refund (200 pts)

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
0

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 trial
shanesuebsahakarnCommented:
Bleah :P Agreed though, the original question needed more elaboration and despite all the comments, there was no feedback from the questioner.
0
nexusnationCommented:
Shane,

As 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
0
shanesuebsahakarnCommented:
Nex,

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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
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.