VBA Express : Access - Round up a value using the Integer or Round function.

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

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

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.