Decimal Entry Rounds Down - Want to Round Up

[JessicaGomo]

On one of my Access forms, a specific textbox rounds a value down if the user enters the decimal. The table field bound to it, is a long integer as to not accept decimal values.

For example = user entered .5 rounds to 0
user entered 10.5 round to 10

I would like this to behave in the opposite manner and always round up, but how??? Since users are estimating their hours for specific tasks, I would prefer any decimal value to round to the next integer. I have tried many adjustments to get this working to no avail. I did come across something about key press for decimal and to disallow user to even enter a decimal in this textbox. I would be fine with that solution as well, but could not implement as seen.

I appreciate any guidance in advance, thank you.

 

[jdraw]

From Allen Browne

Rounding up

To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this:
    - Int( - [MyField])

As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.

To round up to the higher cent, multiply by -100, round, and divide by -100:
    Int(-100 * [MyField]) / -100

 

[JessicaGomo]

This is probably an obvious answer...where does this go? I tried Before Update and On Change events but could not get it to work. Thanks.

 

[jdraw]

Can you give us the 30,000 ft overview of what you are doing?
I don't think you would be using the ONChange event.
Can you show us something specific?

It would seem
- Int( - [MyField]) where myField is you textbox value possibly in after update of text box.

 

[JessicaGomo]

I have attached an image of the form. The only textbox the user can edit is the "My Estimated Hours" field.

 

[jdraw]

What is the code behind the Calculate button. It seems that's where the roundUp code would go.
************ UNTESTED *********
Dim RoundUp as Long
RoundUp = -Int(-Me.textbox.Value)

Msgbox RoundUp should give you the value for testing.

 

[CJ_London]

you are probably suffering from bankers rounding which is the default rounding access will use when converting a decimal value to integer/long.

What happens if you enter 9.5? I would expect it to round to 10

see this link
https://msdn.microsoft.com/en-us/library/s2dy91zy.aspx

and in particular

If the fractional part is exactly 0.5, the integer conversion functions round it to the nearest even integer. For example, 0.5 rounds to 0, and 1.5 and 2.5 both round to 2. This is sometimes called banker's rounding, and its purpose is to compensate for a bias that could accumulate when adding many such numbers together.

If you don't want decimal values, I would use a validation rule.

If you still require 'rounded' decimals to round all '.5' up, then in the control afterupdate event put

mycontrol=clng(eval(mycontrol.text & "+0.01"))

 

[JessicaGomo]

I did not know about bankers rounding, thank you for explaining! Your solution worked perfectly, thanks much.