ACC: How to Round a Number Up or Down by a Desired Increment
ID: Q155696
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article shows you how to create a procedure that rounds a number
up or down by a specified increment. For example, given the number 3.23,
rounding to the nearest .05 results in the number 3.25. The procedure in
this article accepts any positive rounding increment as a parameter. In
addition to rounding numbers to the nearest fractional amount, you can
also round to whole numbers, such as 1, 10, or 100.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
MORE INFORMATION
The following example creates a procedure called RoundToNearest, which
accepts three parameters:
Parameter Value
--------- ---------------------------------------------------------
Amt The numeric value you want to round
RoundAmt The increment to which Amt will be rounded
Direction Constant indicating which direction to round (up or down)
For example, RoundToNearest(3.33, 0.1, vb_roundup) returns the value 3.4.
- Create a module and type the following lines in the Declarations
section:
Option Explicit
Public Const vb_roundup = 1
Public Const vb_rounddown = 0
NOTE: In versions 1.x and 2.0, use the word "Global" instead of
"Public."
- Create the following procedure.
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code in Access Basic.
Function RoundToNearest (Amt As Double, RoundAmt As Variant, _
Direction As Integer) As Double
On Error Resume Next
Dim Temp As Double
Temp = Amt / RoundAmt
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
If Direction = vb_rounddown Then
Temp = Int(Temp)
Else
Temp = Int(Temp) + 1
End If
RoundToNearest = Temp * RoundAmt
End If
End Function
- To test this function, type each of the following lines in the Debug
window (or Immediate window in 1.x and 2.0), and then press ENTER.
?RoundToNearest(1.36, 0.25, vb_roundup)
Note that the procedure returns 1.5.
?RoundToNearest(1.36, 0.05, vb_rounddown)
Note that the procedure returns 1.35.
?RoundToNearest(1.36, 0.75, vb_roundup)
Note that the procedure returns 1.5, which is two increments of 0.75.
NOTE: To use the above function in the ControlSource property of a control
on a form, you will need to replace the constants vb_roundup and
vb_rounddown with their integer values.
REFERENCES
For more information about rounding, please see the following articles in
the Microsoft Knowledge Base:
Q97524 Round or Truncate Values to Desired Number of Decimals
Q111781 Rounding Errors Using Floating-Point Numbers
Additional query words:
factor cents dollars quarter dime nickel
Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|