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:

  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:

roundup example.zip 12.65KB 

Approved by mdmackillop


This entry has been viewed 111 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express