<div>
For example:<br />
<br />
?-Int(-2.01)<br />
&nbsp;Returns 3<br />
<br />
mx
</div>


<div>
Just for clarity:<br />
<br />
-Int(-(YourNumber))
</div>


<div>
@mx<br />
took me a bit to figure out what you were doing!<br />
&lt;Just for clarity:&gt; and no points<br />
Int() bumps a value <i>down</i> to the nearest integer<br />
Negative values with fractions (ie -2.01) go down to -3<br />
Using that fact, @mx suggests multiplying your value by -1, coercing it to integer and then multiplying again by -1<br />
Neat!<br />
<br />
Public Function RoundItUp()<br />
'coerce the inputbox string to single, just to demo it<br />
'replace (CSng(InputBox(&quot;some number&quot;))) with some variable in production<br />
RoundItUp = -1 * Int(-1 * (CSng(InputBox(&quot;some number&quot;))))<br />
MsgBox RoundIt<br />
<br />
End Function
</div>


<div>
That trick is from Gustav ... thanks to him.<br />
<br />
mx
</div>


<div>
Works as long as the original value is positive.
</div>


<div>
There was no indication otherwise.<br />
<br />
mx
</div>


<div>
Joe,<br />
<br />
Just wanted to clarify for the OPs sake. &nbsp;First time I saw you use this, I added it to my kit-bag.
</div>


<div>
Again ... Cactus_Data supplied this trick.<br />
<br />
mx
</div>


<div>
Thanks Nick for clearing that up. I wandered what was going on in that - * - thing. Very original<br />
<br />
DoDahD was simplest. <br />
<br />
Shared. Thank you both
</div>


<div>
Why ... would you want to add 0.5 when a simple function does it ?<br />
<br />
mx
</div>


<div>
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<br />
<br />
Public Function RoundItUp()<br />
<br />
Dim SomeNumber As Single<br />
'coerce the inputbox string to single, just to demo it<br />
'replace (CSng(InputBox(&quot;some number&quot;))) with some variable in production<br />
'This takes advantage of rounding by adding a half<br />
'Anything above .0 will round up to the desired number<br />
'Anything above .5 will not gain enough to round up, but will round down to the desired number<br />
<br />
SomeNumber = CSng(InputBox(&quot;some number&quot;))<br />
If CInt(SomeNumber) &lt;&gt;&nbsp;SomeNumber Then<br />
&nbsp; &nbsp; RoundItUp = Round(SomeNumber + 0.5, 0)<br />
End If<br />
MsgBox RoundItUp<br />
<br />
End Function
</div>


<div>
&gt; Excel functions can be added to Access to provide the roundup ..<br />
<br />
That is not necessary and requires Excel to be installed.<br />
<br />
&gt;&nbsp;Old school technique is to add .5 before rounding ..<br />
<br />
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.<br />
<br />
&gt;&nbsp;.. @mx suggests multiplying your value by -1, coercing it to integer and then multiplying again by -1 ..<br />
<br />
Not exactly multiplying - you just switch the sign.<br />
This is by any means the simplest and fastest method:<br />
<br />
lngInteger = -Int(-YourNumber)<br />
<br />
&gt;&nbsp;Works as long as the original value is positive. <br />
<br />
No, it works for any value:<br />
<br />
i = -Int(-2.8) =&gt; i = -2<br />
<br />
&gt;&nbsp;That trick is from Gustav ... thanks to him.<br />
<br />
Yes, here, but I didn't invent it. It is an old trick from BASIC.<br />
<br />
/gustav<br />
<br />

</div>


<div>
Hi All<br />
<br />
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.<br />
Interesting comments. Thank you<br />
<br />

</div>


<div>
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)<br />
<br />
Cheers.
</div>


<div>
&quot;My concern with mx solution was that a user might put a negative value in a field &quot;<br />
No. &nbsp;You would add the minus sign in the AfterUpdate event of a text box, &nbsp;There is no extra work for the user.<br />
<br />
This is as simple and fastest as it gets.<br />
<br />
mx
</div>


<div>
/gustav<br />
&lt;the native Round of VBA is buggy. &gt;<br />
MS Access uses bankers rounding<br />
<a href="http://allenbrowne.com/round.html#BankersRounding" rel="ugc">http://allenbrowne.com/round.html#BankersRounding</a><br />
which, if you are expecting the arithmetic rounding we all learned in school, is unexpected.<br />
I don't know that it's a bug, per se, but it is not arithmetic rounding.<br />
<br />
Is there a bug in Round() over and above that, that I am unaware of?
</div>


<div>
<br />
DatabaseDek:<br />
<br />
User enters 2.09 in text box and Tabs out or hits Enter<br />
Private Sub txtNumber_AfterUpdate<br />
&nbsp; &nbsp;Me.txtNumber = -Int(-(Me.txtNumber))<br />
End Sub<br />
<br />
Result is 3 in the text box.<br />
<br />
&quot;The only way to change a negative to a positive in mathmatics is to multiply it by another negative&quot;<br />
I don't see any multiplication sign here :-)<br />
<br />
mx<br />
<br />

</div>


<div>
It's the old mathematics shorthand<br />
-1*(x) = -(x)<br />
But it took me a second or two looking at it to realize that's what it was.<br />
Which is why what I posted was a little more verbose. :)
</div>


<div>
Round is buggy as documentated here but that is not related to Banker's Rounding (which is a rounding method, not a bug):<br />
<br />
<a href="http://www.xbeat.net/vbspeed/c_Round.htm" rel="ugc">VBspeed / VB6 to VB5 / Round</a><br />
<br />
The only native function of Access VBA that performs a perfect 4/5 rounding is Format.<br />
That is taken advantage of and demonstrated in the above link at Round16.<br />
Highly recommended reading.<br />
<br />
/gustav
</div>


<div>
I don't see any multiplication sign here :-)<br />
<br />
You are correct (- - 6) = 6 but effectively it is addition, it means - + - 
</div>


<div>
I also suspect that <br />
<br />
6(1+1) would read 12 so the multiplication is implicit. But I am not sure
</div>


<div>
Old school solution has one weakness. &nbsp;If you a .5 it will round 1 to 2, so you would need to use .49, &nbsp;.499, etc. that was one more decimal place than you permit in the field. &nbsp;For example, a 4 place decimal number would use .49999 for this technique.<br />
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. &nbsp; If you are using Access 2010, you could add a calculated fiedl to the original table to do the rounding for you.
</div>


<div>
This round is NOT a VB issue so banker's rounding does not apply. &nbsp; See attached db with calculated field in table. <a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/509815/Database5.accdb" target="_blank" class="file-inline" title="Table with calculated rounded field. (396 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Database5.accdb</a>
</div>


Database5.accdb

<div>
I don't see your point with 0.499 and so on. This is a simple round up.<br />
<br />
Wether you do the round up in a query or as a calculated field in a report doesn't change this.<br />
<br />
/gustav
</div>


<div>
@DoDahD<br />
&lt;This round is NOT a VB issue so banker's rounding does not apply. &nbsp; See attached db with calculated field in table. &gt;<br />
I cannot open your accdb as I am on A2003, but...<br />
<br />
If you use Round() in MS Access, you get bankers' rounding and not arithmetic rounding.<br />
As noted earlier (<a href="http://allenbrowne.com/round.html#BankersRounding" rel="ugc">http://allenbrowne.com/round.html#BankersRounding</a>)<br />
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.<br />
In all other cases Round(SomeValue + 0.5,0) will round SomeValue to the next highest integer.<br />
In the integer case, bankers rounding comes into play and you do not get the desired result<br />
Round(2 + 0.5,0) = 2, which is what the OP wanted<br />
Round(3 + 0.5,0) = 4, which is not<br />
Hence, you handle the integer case and tell the function to do nothing to integer input values<br />
<br />
In short, I am not sure what you are saying or why you are saying it as I think it was exhaustively covered.<br />
<br />
But maybe I am missing something.<br />
As I said, I can't open your example to illuminate your post :)
</div>


<div>
<div class="content wysiwyg-content">
I have a calculated field in a report that should read the next higher Integer number . I Need<br />
<br />
1.5 to result in 2<br />
1.1 to result in 2<br />
1.9 to result in 2<br />
2.01 to result in 3 etc<br />
Round will round up or down. I need RoundUp but there is no function that I can see<br />

</div>
</div>


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


Round Up problem

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.