How to Round Integers

[RamanS]

I have this formula in my code

Me!QuoteDetails Subform.Form![Quantity] = Round((Me.txtTo talTrunks / 2), 0)

if me.txtTotalTrun ks = 5, then the result i get is 2.

But the actual value is 2.5. I want to this number to be rounded to 3 and not 2.

That means if the result is a decimal number the quanitity should not decrease but increase for example:

if the result = 5.6 or 5.9 then quantiy should be 6
if the result = 5.1 or 5.2, then quantity should be 6 also.

Here are the properties:
[Quantity] = General Number, 0 decimal places
Me.txtTotalTrun ks = General Number, 0 decimal places

Please help.

[JKing]

Hi, unfortunately access does not have a function that I know of to always roundup. However we can use Int() or Fix() to take only the integer value of a number and add 1 to acheive rounding up.

Me!QuoteDetailsSubform.Form![Quantity] = Int(Me.txtTotalTrunks) + 1

Int and Fix differ when it comes to negatives. Here's what the Microsoft helpfile says about Int() and Fix().

"Both Int and Fix remove the fractional part of number and return the resulting integer value.

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8."

So if you're using negatives youll need to choose the right one for your desired results. If not just choose which you prefer as they will produce the same results for positive values.

[missinglinq]

Another approach would be this function. Sorry, don't remember where I got it from!

Create a new standard module with this code
[CODE=vb]Public Function RoundUpTotal(By Val dblNumber As Double, ByVal intDecimals As Integer) As Double

' : 0.5 is rounded up
'Parameters : dblNumber - number to round
' : intDecimals - number of demal places
' to round to

' : (positive for right of decimal, negative for left
'Returns : Rounded number

Dim dblFactor As Double
Dim dblTemp As Double ' Temp var to prevent rounding problems in INT()

dblFactor = 10 ^ intDecimals
dblTemp = dblNumber * dblFactor + 0.5
RoundUpTotal = Int("" & dblTemp) / dblFactor

End Function
[/CODE]Then, in code

[CODE=vb]RoundUpTotal(Yo urNumber,Number OfDecimalsToRou ndTo)[/CODE] If you want to round up to the nearest whole number enter 0 (zero) for the
NumberOfDecimal sToRoundTo argument. Remember, when asked to save and name the module, use anything except RoundUpTotal!

If the line numbers appear in code when copying and pasting code, remove them!

[FishVal]

I have this formula in my code

Me!QuoteDetails Subform.Form![Quantity] = Round((Me.txtTo talTrunks / 2), 0)

if me.txtTotalTrun ks = 5, then the result i get is 2.

But the actual value is 2.5. I want to this number to be rounded to 3 and not 2.

That means if the result is a decimal number the quanitity should not decrease but increase for example:

if the result = 5.6 or 5.9 then quantiy should be 6
if the result = 5.1 or 5.2, then quantity should be 6 also.

Here are the properties:
[Quantity] = General Number, 0 decimal places
Me.txtTotalTrun ks = General Number, 0 decimal places

Please help.

Hi.

The simplest known to me way to do it is the following

Round(YourValue +0.5)

This will round a value to the upper integer.

Good luck.

P.S. Sorry Linq. Having not read you post thoroughly, I've given the same solution. The only thing I can suggest to enhance the code is to declare NumberOfDecimal sToRoundTo argument as Optional Variant and set it to 0 by default.

P.S.S. BTW, Linq, the code will round an input value to the nearest integer not the upper. Using Round function instead of Int will give the desired result.

[missinglinq]

I'm confused by your statement

the code will round an input value to the nearest integer not the upper. Using Round function instead of Int will give the desired result.

With a stating value of 1.5 the RoundUpTotal() function will yield 2 (which was the OP's goal) while the Round() function will yield 1.

The whole question here is what is the "nearest" integer. Access says the nearest to 1.5 is 1. The OP wants it to be 2, which BTW is the way I was taught to round back in the one-room schoolhouse days.

[FishVal]

I'm confused by your statement
With a stating value of 1.5 the RoundUpTotal() function will yield 2 (which was the OP's goal) while the Round() function will yield 1.

The whole question here is what is the "nearest" integer. Access says the nearest to 1.5 is 1. The OP wants it to be 2, which BTW is the way I was taught to round back in the one-room schoolhouse days.

Maybe I'm missing something but RamanS said

if the result = 5.6 or 5.9 then quantiy should be 6
if the result = 5.1 or 5.2, then quantity should be 6 also.

[missinglinq]

You're exactly right! I saw the

if the result = 5.6 or 5.9 then quantiy should be 6

and missed the

if the result = 5.1 or 5.2, then quantity should be 6 also