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
@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
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
> 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)
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.
/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?
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. :)
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
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.
@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
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
-Int(-YourNumber)
mx