ACC: Rounding a Number Up or Down by a Desired Increment

Last reviewed: August 29, 1997
Article 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 demonstrates 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.

  1. 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."

  2. 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
    
    

  3. 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:

   ARTICLE-ID: Q97524
   TITLE     : ACC: Round or Truncate Values to Desired Number of Decimals

   ARTICLE-ID: Q111781
   TITLE     : ACC: Rounding Errors Using Floating-Point Numbers


Additional query words: factor cents dollars quarter dime nickel
Keywords : kbprg PgmHowTo SynFnc
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.