rounding

  • Thread starter Thread starter Jim Smith
  • Start date Start date
J

Jim Smith

I have a form with this calculation in the detail section:

=([cdleva_1]*([cdlevp_1]/100))+([cdleva_2]*([cdlevp_2]/100))

In the form footer I have:

=Sum(([cdleva_1]*([cdlevp_1]/100))+([cdleva_2]*([cdlevp_2]/100)))

the amounts shown when expanded to 4 decimal places are:
240.1600
240.1600
166.1280

With a total of:
646.4440

What I'd like to see is:
646.45

So my question are 1) why don't I see a total of 646.4480? and 2) how
do I fix this?

TIA
Jim
 
Floating point numbers (such as the Single and Double numbers in Access) are
only approxmations. Summing approximations can result in rounding errors. To
get results that add up correctly, round each one, and ideally convert to a
type such as Currency (fixed point, does not suffer from rounding errors).

The simplest way to do this be to create a query, and perform the
calculation there with rounding. Use the query as the RecordSource of your
form. Then the rounded numbers will sum correctly for your form footer.

1. Create a new query into your table.
2. Drag the fields you want into the query grid.

3. Type something like this into the Field row, in a fresh column:
Amount: CCur(Round(Nz(([cdleva_1]*[cdlevp_1]/100) +
([cdleva_2]*[cdlevp_2]/100),0),2))

4. Save the query.

5. Open your form in design view. Open the Properties box (View menu), and
set the RecordSource property (Data tab of Properties box when looking at
the properties of the form) to the name of the query.

6. Change the Control Source of your calculated field to:
Amount
i.e. the name of the calculated field in the query.
Change the Name property of this text box to Amount also.
Set its Format property to display the format you want.

7. Change the Control Source of the calculated field in the form footer to:
=Sum([Amount])
Set its Format property as desired.

Since the calculated is rounded in the query, the results should add up
correctly.
 
Back
Top