ACC: Round or Truncate Values to Desired Number of DecimalsLast reviewed: August 29, 1997Article ID: Q97524 |
The information in this article applies to:
SUMMARYModerate: Requires basic macro, coding, and interoperability skills. The Format property of a control can round a Number or Currency field to the desired number of decimal places. However, this does not change the underlying data, which may contain additional digits that the control does not display. If you add the values in this control, the sum is based on the actual values and not on the displayed values. This may make the total seem inaccurate. This article describes four user-defined functions that you can use to round or truncate data to two decimal places so that the displayed and formatted value and the actual numeric or currency data are the same. 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 INFORMATIONThe functions are presented in two styles. The first style is appropriate for the AfterUpdate property of a form control to ensure that the data entered matches the data that is displayed. The second style is for use in expressions and calculated controls. To round or truncate numbers to two decimal places, create a new module and add the following functions.
'****************************************************** ' Declarations section of the module '****************************************************** Option Explicit Const Factor = 100 '===================================================== ' RoundAU and TruncAU are designed to be added to the ' AfterUpdate property on a form control. '===================================================== Function RoundAU(X As Control) X = Int(X * Factor + .5) / Factor End Function Function TruncAU(X As Control) X = Int(X * Factor) / Factor End Function '===================================================== ' RoundCC and TruncCC are designed to be used in ' expressions and calculated controls on forms and reports. '===================================================== Function RoundCC(X) RoundCC = Int (X * Factor + 0.5) / Factor End Function Function TruncCC(X) TruncCC = Int (X * Factor) / Factor End Function Examples of Using the Round and Truncate FunctionsThe following examples use the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). CAUTION: Following the steps in these examples will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.
Example 1Use the TruncAU() function to the AfterUpdate property of a form:
Example 2Use the RoundCC() function with an expression in a report's group footer. This example assumes that you have already created the Rounding module in step 2 of Example 1:
10 = 1 decimal place 100 = 2 decimal places 1000 = 3 decimal places, etc. LimitationsThese functions should only be used with Currency data. If used with Double or Single numbers, you may still receive minor rounding errors. The reason for this is that Single and Double numbers are floating point. They cannot store an exact binary representation of decimal fractions. Therefore there will always be some error. However, Currency values are scaled integers and can store an exact binary representation of fractions to 4 decimal places.
|
Additional query words: int trunc precision rounding
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |