ACC2000: Round or Truncate Currency Values to the Intended Number of Decimals

The Format property of a control can round a Number or Currency field to the number of decimal places that you want. 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 behavior may make the total seem inaccurate.

This article demonstrates how to create two user-defined functions to truncate data to two decimal places so that the displayed and formatted value and the actual numeric or currency data are the same. It also shows you how to use the built-in Round() function to round the data to the intended number of decimal places.

If your data has more than two digits after the decimal point, you can either round the result to two decimal places or truncate the number after two decimal places without rounding. To format the data so that the actual value and the displayed value both have two decimal places (especially for currency), you can use one of the following functions in the After Update property of form control objects or in expressions and calculated controls on forms and reports.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Method 1: Functions for Use in the After Update Property of Form Controls

Truncating a Value to Two Decimal Places

To truncate numbers to two decimal places during data entry, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. Create a new module and type the following functions:

   '******************************************************
   ' Declarations section of the module
   '******************************************************
   
   Option Explicit
   Const Factor = 100
   
   '=====================================================
   ' TruncAU is designed to be added to the
   ' AfterUpdate property on a form control.
   '=====================================================
   
   Function TruncAU(X As Control)
      X = Int(X * Factor) / Factor
   End Function
   					

3. On the File menu, click Close and Return to Microsoft Access.
4. Open the Products form in Design view, and then add the Visual Basic TruncAU() function to the AfterUpdate property of the UnitPrice field:
   AfterUpdate: =TruncAU([UnitPrice])

Rounding a Value to Two Decimal Places

To round numbers to two decimal places during data entry, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. Open the Products form in Design view, and add the Visual Basic Round() function to the AfterUpdate property of the UnitPrice field:
   AfterUpdate: =Round([UnitPrice],2)
   If you accidentally type $23.055 instead of $23.05, the TruncAu() function catches the mistake and changes the value to $23.05. If you use the Round() function instead, the function changes the value to $23.06. If you use neither function, the value is displayed as $23.06, but the entered value, $23.055, is used in any calculations.

Method 2: Functions for Use in Expressions and Calculated Controls on Forms and Reports

To truncate numbers to two decimal places in the Group footer of a report, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. Create a new module and type the following functions:

   '******************************************************
   ' Declarations section of the module
   '******************************************************
   
   Option Explicit
   Const Factor = 100
   '=====================================================
   ' TruncCC is designed to be used in
   ' expressions and calculated controls on forms and reports.
   '=====================================================
   
   Function TruncCC(X)
      TruncCC = Int (X * Factor) / Factor
   End Function
   					

3. On the File menu, click Close and Return to Microsoft Access.
4. Open the Summary Of Sales By Year report in Design view, and then modify the following two text boxes in the report's Group footers with the following properties:
   Control Name: QuarterSales
   ControlSource: =Sum(TruncCC([SubTotal]))
   
   Control Name: YearTotal
   ControlSource: =Sum(TruncCC([SubTotal]))
   If you use Round(), the report sums the values that are displayed in the report, even though the actual values may contain hidden digits.

NOTE: To change the number of decimal places that the functions use, open the module containing the truncation functions and change the value of the global constant Factor as follows:

Limitations

The user-defined 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 is always some error. However, Currency values are scaled integers and can store an exact binary representation of fractions to four decimal places.