-Int(-YourNumber)

mx

Solved

Posted on 2011-10-04

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

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

27 Comments

Old school technique is to add .5 before rounding. This would force a RoundUP without having to expand the reference library.

took me a bit to figure out what you were doing!

<Just for clarity:> and no points

Int() bumps a value

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

Just wanted to clarify for the OPs sake. First time I saw you use this, I added it to my kit-bag.

DoDahD was simplest.

Shared. Thank you both

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

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

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

Cheers.

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

<the native Round of VBA is buggy. >

MS Access uses bankers rounding

http://allenbrowne.com/rou

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?

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

-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. :)

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

You are correct (- - 6) = 6 but effectively it is addition, it means - + -

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.

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

/gustav

<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/ro

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 :)

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Create connection string from Access to SQL Server sitting on a VMWare virtual machine | 21 | 77 | |

How get a scanned image on a report | 4 | 15 | |

64 bit vba timer module | 2 | 21 | |

dcount multiple criteria | 19 | 19 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!