Round Up problem

I have a calculated field in a report that should read the next higher Integer number . I Need

1.5 to result in 2
1.1 to result in 2
1.9 to result in 2
2.01 to result in 3 etc
Round will round up or down. I need RoundUp but there is no function that I can see
Derek BrownMDAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

-Int(-YourNumber)

mx
0
Richard DanekeTrainerCommented:
Excel functions can be added to Access to provide the roundup you mention.

Old school technique is to add .5 before rounding.  This would force a RoundUP without having to expand the reference library.
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For example:

?-Int(-2.01)
 Returns 3

mx
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just for clarity:

-Int(-(YourNumber))
0
Nick67Commented:
@mx
took me a bit to figure out what you were doing!
<Just for clarity:> and no points
Int() bumps a value down to the nearest integer
Negative values with fractions (ie -2.01) go down to -3
Using that fact, @mx suggests multiplying your value by -1, coercing it to integer and then multiplying again by -1
Neat!

Public Function RoundItUp()
'coerce the inputbox string to single, just to demo it
'replace (CSng(InputBox("some number"))) with some variable in production
RoundItUp = -1 * Int(-1 * (CSng(InputBox("some number"))))
MsgBox RoundIt

End Function
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That trick is from Gustav ... thanks to him.

mx
0
Dale FyeCommented:
Works as long as the original value is positive.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
There was no indication otherwise.

mx
0
Dale FyeCommented:
Joe,

Just wanted to clarify for the OPs sake.  First time I saw you use this, I added it to my kit-bag.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Again ... Cactus_Data supplied this trick.

mx
0
Derek BrownMDAuthor Commented:
Thanks Nick for clearing that up. I wandered what was going on in that - * - thing. Very original

DoDahD was simplest.

Shared. Thank you both
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Why ... would you want to add 0.5 when a simple function does it ?

mx
0
Nick67Commented:
Adding a half works too, but it's more complex because you have to handle for even values and the fact that Access uses banker's rounding

Public Function RoundItUp()

Dim SomeNumber As Single
'coerce the inputbox string to single, just to demo it
'replace (CSng(InputBox("some number"))) with some variable in production
'This takes advantage of rounding by adding a half
'Anything above .0 will round up to the desired number
'Anything above .5 will not gain enough to round up, but will round down to the desired number

SomeNumber = CSng(InputBox("some number"))
If CInt(SomeNumber) <> SomeNumber Then
    RoundItUp = Round(SomeNumber + 0.5, 0)
End If
MsgBox RoundItUp

End Function
0
Gustav BrockCIOCommented:
> Excel functions can be added to Access to provide the roundup ..

That is not necessary and requires Excel to be installed.

> Old school technique is to add .5 before rounding ..

True, but that still requires a function to perform a 4/5 round but, unfortunately, the native Round of VBA is buggy. However, it should work for rounding to an integer.

> .. @mx suggests multiplying your value by -1, coercing it to integer and then multiplying again by -1 ..

Not exactly multiplying - you just switch the sign.
This is by any means the simplest and fastest method:

lngInteger = -Int(-YourNumber)

> Works as long as the original value is positive.

No, it works for any value:

i = -Int(-2.8) => i = -2

> That trick is from Gustav ... thanks to him.

Yes, here, but I didn't invent it. It is an old trick from BASIC.

/gustav

0
Derek BrownMDAuthor Commented:
Hi All

My concern with mx solution was that a user might put a negative value in a field (it shouldn't be allowed to happen but if users can find a way they will). I also suspected that a negative would also work, but simply didn't have the time to check it.
Interesting comments. Thank you

0
Derek BrownMDAuthor Commented:
The only way to change a negative to a positive in mathmatics is to multiply it by another negative. (apart from simple addition of course)

Cheers.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"My concern with mx solution was that a user might put a negative value in a field "
No.  You would add the minus sign in the AfterUpdate event of a text box,  There is no extra work for the user.

This is as simple and fastest as it gets.

mx
0
Nick67Commented:
/gustav
<the native Round of VBA is buggy. >
MS Access uses bankers rounding
http://allenbrowne.com/round.html#BankersRounding
which, if you are expecting the arithmetic rounding we all learned in school, is unexpected.
I don't know that it's a bug, per se, but it is not arithmetic rounding.

Is there a bug in Round() over and above that, that I am unaware of?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

DatabaseDek:

User enters 2.09 in text box and Tabs out or hits Enter
Private Sub txtNumber_AfterUpdate
   Me.txtNumber = -Int(-(Me.txtNumber))
End Sub

Result is 3 in the text box.

"The only way to change a negative to a positive in mathmatics is to multiply it by another negative"
I don't see any multiplication sign here :-)

mx

0
Nick67Commented:
It's the old mathematics shorthand
-1*(x) = -(x)
But it took me a second or two looking at it to realize that's what it was.
Which is why what I posted was a little more verbose. :)
0
Gustav BrockCIOCommented:
Round is buggy as documentated here but that is not related to Banker's Rounding (which is a rounding method, not a bug):

VBspeed / VB6 to VB5 / Round

The only native function of Access VBA that performs a perfect 4/5 rounding is Format.
That is taken advantage of and demonstrated in the above link at Round16.
Highly recommended reading.

/gustav
0
Derek BrownMDAuthor Commented:
I don't see any multiplication sign here :-)

You are correct (- - 6) = 6 but effectively it is addition, it means - + -
0
Derek BrownMDAuthor Commented:
I also suspect that

6(1+1) would read 12 so the multiplication is implicit. But I am not sure
0
Richard DanekeTrainerCommented:
Old school solution has one weakness.  If you a .5 it will round 1 to 2, so you would need to use .49,  .499, etc. that was one more decimal place than you permit in the field.  For example, a 4 place decimal number would use .49999 for this technique.
Since you mention you are using the field in a report, you can add a calculated text box to the report or to its underlying query to do the rounding.   If you are using Access 2010, you could add a calculated fiedl to the original table to do the rounding for you.
0
Richard DanekeTrainerCommented:
This round is NOT a VB issue so banker's rounding does not apply.   See attached db with calculated field in table. Database5.accdb
0
Gustav BrockCIOCommented:
I don't see your point with 0.499 and so on. This is a simple round up.

Wether you do the round up in a query or as a calculated field in a report doesn't change this.

/gustav
0
Nick67Commented:
@DoDahD
<This round is NOT a VB issue so banker's rounding does not apply.   See attached db with calculated field in table. >
I cannot open your accdb as I am on A2003, but...

If you use Round() in MS Access, you get bankers' rounding and not arithmetic rounding.
As noted earlier (http://allenbrowne.com/round.html#BankersRounding)
And I noted in ID:36913850, if you do use 'add a half and then round' you have to handle the integer input case to make the function work the way you want.
In all other cases Round(SomeValue + 0.5,0) will round SomeValue to the next highest integer.
In the integer case, bankers rounding comes into play and you do not get the desired result
Round(2 + 0.5,0) = 2, which is what the OP wanted
Round(3 + 0.5,0) = 4, which is not
Hence, you handle the integer case and tell the function to do nothing to integer input values

In short, I am not sure what you are saying or why you are saying it as I think it was exhaustively covered.

But maybe I am missing something.
As I said, I can't open your example to illuminate your post :)
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.