|
|
|
|
|
|
Access
|
Round up a value using the Integer or Round function.
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
OBP
|
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:
|
instructions for use
|
Dim numvalue As Double
numvalue = [Value]
If (numvalue - Int(numvalue)) = 0 ThenExit Sub
Else
numvalue = Int(numvalue) + 1
MsgBox numvalue
End If
Dim numvalue As Double
numvalue = [Value]
If (numvalue - Int(numvalue)) = 0 Then Exit Sub
Else
numvalue = Round(numvalue + 0.5)
MsgBox numvalue
End If
|
How to use:
|
- Open the form in Design mode.
- Add the command button to your Form by clicking the "Command button" Icon on the Toolbox Menu.
- On the first page of the Command Button Wizard select Cancel.
- Right Click on the newly created Button and choose Properties.
- In the the properties window click on the "Event" tab.
- Locate "On Click" where it says "Event Procedure" and click just to the right.
- Click on the three little dots that show up to the right of the event procedure.
-
- Click "Code Builder" to bring up the Visual Basic Editor (VBE) with the Code for the button.
- Copy the above code.
- Paste the code between the Private Sub xxx_click() and End Sub lines.
- Change all instances of [Value] to the field name on the form that you want rounded up.
- Exit the VBE, close the Command Button's properties window, and save your changes.
-
- 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
- 'Method 1
- Dim numvalue As Double
- numvalue = [Value]
-
- If (numvalue - Int(numvalue)) <> 0 Then 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.
-
- Dim numvalue As Double
- numvalue = [Value]
-
- If (numvalue - Int(numvalue)) <> 0 Then 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
|
Test the code:
|
- Open the form and place a value in the appropriate field.
- Click the Command Button to round the value.
|
Sample File:
|
roundup example.zip 12.65KB
|
Approved by mdmackillop
|
This entry has been viewed 118 times.
|
|