Report field data type problem 2

CHTHOMAS · Nov 1, 1999

I have a field in my report whose data type properties are set as follows. 
Format->Standard 
Decimal Places->2 
I set the same type of properties for my table and query. 
I am using the function sum to find the sum of the field in the report. My problem is i am getting 1 Penny/fill difference for the sum. I believe it may be due to the rounding of values, but not sure about how to solve it. Any suggestions or solutions will be highly appreciated. 
Thanks in advance 
Charley

dmccarthy · Nov 2, 1999

Sounds like a rounding up/down problem try the currency format or force the result with format £ #,#0.00;£ -#,#0.00;£ 0.00;£ 0.00 (+ve,-ve,zero and null formats)

Crownknox · Apr 27, 2000

I am having the same problem with the currency round up/or down one penny.  I'm try to take the sum of several values in a query.  the totals that are in the query come from the multiplication of unitprices that have 4 decimals (0.0234)etc.  When this unitprice gets multiplied by the quantity, the sum this single price will be correct, but when I add the sum of several parts prices in a report the sum will be +/- one penny.  Did any one help you with this problem.  If so I would love to hear how I can solve this problem.  reply me at <A HREF="mailto:crownknox@aol.com">crownknox@aol.com</A>  thanks.

harryrich · May 3, 2000

The best I was able to do was to round the fractional penny results using the computation: CCur(Int(X*100+0.5)/100).  It depends on X being small enough to multiply safely by 100.  The intermediate results in this computation seem to be double precision floating point.  I'm hoping that the error doesn't make it back into the third and fourth digits of the currency representation when it converts.  I haven't seen any wierd numbers yet.

CHTHOMAS · May 3, 2000

The solution to the above mentioned problem is as follows. I did get it some where from the net, probably Microsoft, but don't remember correctly.   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:   1.Open the sample database Northwind.mdb.   2.Create a new module called Rounding, and type the procedures in the preceding section.   3.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:   1.Open the sample database Northwind.mdb.   2.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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Report field data type problem 2

CHTHOMAS

Programmer

dmccarthy

Programmer

Crownknox

Technical User

harryrich

Programmer

CHTHOMAS

Programmer

Similar threads

Part and Inventory Search

Sponsor