Article ID: 210564 - View products that this article applies to.
This article was previously published under Q210564
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
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 PlacesTo truncate numbers to two decimal places during data entry, follow these steps:
Rounding a Value to Two Decimal PlacesTo round numbers to two decimal places during data entry, follow these steps:
Method 2: Functions for Use in Expressions and Calculated Controls on Forms and ReportsTo truncate numbers to two decimal places in the Group footer of a report, follow these steps:
10 = 1 decimal place
100 = 2 decimal places
1000 = 3 decimal places, and so on
LimitationsThe 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.