ACC: Round or Truncate Values to Desired Number of Decimals
ID: Q97524
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
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 may make the
total seem inaccurate.
This article shows you how to create four user-defined functions 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 INFORMATION
The 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 Functions
The 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 1
Use the TruncAU() function to the AfterUpdate property of a form:
- Open the sample database Northwind.mdb.
- Create a new module called Rounding, and type the procedures in the
preceding section.
- Open the Products form in Design view, and add the TruncAU() function to the AfterUpdate property of the UnitPrice field (or Unit Price field in
version 2.0 or earlier):
Form: Products
--------------
Control Name: Unit Price
AfterUpdate: =TruncAU([UnitPrice])
If a user accidentally enters $23.055 instead of $23.05, the TruncAu()
function catches the mistake and changes the value to $23.05. If you
use the RoundAu() 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.
Example 2
Use 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:
- Open the sample database Northwind.mdb.
- Open the Summary Of Sales By Year report in Design view and use the
RoundCC() function in the ControlSource property of two controls in the
report's group footer:
In Microsoft Access 7.0 and 97:
Report: Summary of Sales By Year
--------------------------------
Control Name: QuarterSales
ControlSource: =Sum(RoundCC([SubTotal]))
Control Name: YearTotal
ControlSource: =Sum(RoundCC([SubTotal]))
In Microsoft Access 1.x and 2.0:
Report: Summary of Sales By Year
--------------------------------
Control Name: Total Sales for Quarter
ControlSource: =Sum(RoundCC([Order Amount]))
Control Name: Total Sales for Year
ControlSource: =Sum(RoundCC([Order Amount]))
If you use RoundCC(), the report sums the values 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 Rounding module in Design view and change the value of the global
constant, Factor, as follows:
10 = 1 decimal place
100 = 2 decimal places
1000 = 3 decimal places, and so on
Limitations
These 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
Keywords : kbprg kbusage kbdta AccCon KbVBA
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|