 Access

Round up a value using the Integer or Round function. Ease of Use

Easy

Version tested with

2000, 2002, 2003

Submitted by:

Description:

This code converts a decimal value to an Integer value by rounding it up, never down.

Discussion:

Access does not have a Roundup Function like Excel, so you can use either of these instead, one uses the INTEGER function and the other uses the ROUND function. This Code will have to be attached to a Command Button to carry out the conversion or to the "On Click" event procedure for the Field to be converted providing that the user knows to click the field to carry it out. The instructions and example file assume the use of a Command Buttom.

Code:

```			 'Method 1. Using The Integer function
'set the number variable as a double precision number.
Dim numvalue As Double
numvalue = [Value]
'where [Value] is the field name on the form containing the value to be rounded.

'Checks If the value has anything after the decimal point
'if it does then stop the routine
If (numvalue - Int(numvalue)) = 0 ThenExit Sub

'otherwise, remove the decimal part of the value and add 1 to it to round it up.
Else
numvalue = Int(numvalue) + 1
MsgBox numvalue ' display the new value

'[Value] = numvalue
' remove the ' from in front of the [Value] to make it the rounded up number

End If

'Method 2 suggested by MDmackillop. Using the Round function.
'set the number variable as a double precision number.
Dim numvalue As Double
numvalue = [Value]
'where [Value] is the field name on the form containing the value to be rounded.

'Checks If the value has anything after the decimal point
'if it does then stop the routine
If (numvalue - Int(numvalue)) = 0 Then Exit Sub

'otherwise, add 0.5 to the value which will force the round function to round it up.
Else
numvalue = Round(numvalue + 0.5)
MsgBox numvalue ' display the new value

'[Value] = numvalue
' remove the ' from in front of the [Value] to make it the rounded up number

End If

```

How to use:

1. Open the form in Design mode.
2. Add the command button to your Form by clicking the "Command button" Icon on the Toolbox Menu.
3. On the first page of the Command Button Wizard select Cancel.
4. Right Click on the newly created Button and choose Properties.
5. In the the properties window click on the "Event" tab.
6. Locate "On Click" where it says "Event Procedure" and click just to the right.
7. Click on the three little dots that show up to the right of the event procedure.
8. Click "Code Builder" to bring up the Visual Basic Editor (VBE) with the Code for the button.
9. Copy the above code.
10. Paste the code between the Private Sub xxx_click() and End Sub lines.
11. Change all instances of [Value] to the field name on the form that you want rounded up.
12. Exit the VBE, close the Command Button's properties window, and save your changes.
13. It should be noted that if you do not mind showing Values that do not need rounding up then the Code can be shortened to
14. 'Method 1
15. Dim numvalue As Double
16. numvalue = [Value]
17. If (numvalue - Int(numvalue)) <> 0 Then numvalue = Int(numvalue) + 1
18. MsgBox numvalue ' display the new value
19. '[Value] = numvalue
20. ' remove the ' from in front of the [Value] to make it the rounded up number
21. End If
22. Method 2 suggested by MDmackillop.
23. Dim numvalue As Double
24. numvalue = [Value]
25. If (numvalue - Int(numvalue)) <> 0 Then numvalue = Round(numvalue + 0.5)
26. MsgBox numvalue ' display the new value
27. '[Value] = numvalue
28. ' remove the ' from in front of the [Value] to make it the rounded up number
29. End If

Test the code:

1. Open the form and place a value in the appropriate field.
2. Click the Command Button to round the value.

Sample File:

Approved by mdmackillop

This entry has been viewed 116 times.

Copyright @2004 - 2020 VBA Express