Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

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
0
DatabaseDek
Asked:
DatabaseDek
  • 9
  • 5
  • 5
  • +3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

-Int(-YourNumber)

mx
0
 
Richard DanekeAdjunct FacultyCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
For example:

?-Int(-2.01)
 Returns 3

mx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
Again ... Cactus_Data supplied this trick.

mx
0
 
DatabaseDekAuthor 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 MVP, Access and Data Platform)Commented:
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
 
DatabaseDekAuthor 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
 
DatabaseDekAuthor 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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:

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
 
DatabaseDekAuthor Commented:
I don't see any multiplication sign here :-)

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

6(1+1) would read 12 so the multiplication is implicit. But I am not sure
0
 
Richard DanekeAdjunct FacultyCommented:
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 DanekeAdjunct FacultyCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 5
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now