Total and Round problem?

[Guest]

I have a problem about this. In my detail section, 2 values calucated from
the raw data, then the 3rd value calucated the total. 1st value show 3
decimals(format), and 2nd value show 2 decimals(format), the 3rd value, which
1st + 2nd values, show 2 decimals(format).
1st and 2nd will round to the decimals and print which are ok. But the
problem is the 3rd one. It will add the 1st and 2nd raw data first, then
round to 2 decimals. This made sometimes 1st + 2nd <> 3rd. In this case, how
should I do?

Thank you very much.

Fox

 

[Marshall Barton]

I have a problem about this. In my detail section, 2 values calucated from
the raw data, then the 3rd value calucated the total. 1st value show 3
decimals(format), and 2nd value show 2 decimals(format), the 3rd value, which
1st + 2nd values, show 2 decimals(format).
1st and 2nd will round to the decimals and print which are ok. But the
problem is the 3rd one. It will add the 1st and 2nd raw data first, then
round to 2 decimals. This made sometimes 1st + 2nd <> 3rd. In this case, how
should I do?

This is a common misunderstanding. Round(x) + Round(y)
is not the same as Round(x+y) total.

While Round(x+y) is the more accurate value, most people
mentally add Round(x) + Round(y) and then claim that the
total is wrong.

Either live with the display discrepency (e.g. a holding vat
volume where the total is most important) or change your
total calculation to sum the rounded values (as people are
doing mentally).

 

[SA]

Fox:

The problem is that the values shown when you set the format of a control,
don't impact the underlying data. So, lets say your first value is:

1.23456 -> which when formatted is 1.23

and the second value is:

1.23456 -> which when formatted is 1.23

The total is:

2.46912 -> which when formatted is 2.47

so the numbers don't appear to equal.

To resolve this you have to round the primary values in the underlying query
or recordset that feeds the report. In Access 2002 and later there is a
built in Round function which you can use by wrapping the field in question
with the function in your query as an example:

ValueRnd: Round([YourFieldName], 2)

In previous versions, you need to create a round function in a VBA module
and then, again wrap your field in this function in the underlying query.

Below is such a function, originally created by Ken Getz, that should help
you out.
--
SA
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

---------begin code------------
Public Function Round(ByVal Number As Variant, NumDigits As Long, _
Optional UseBankersRounding As Boolean = False) As Double
'Posted by Ken Getz in microsoft.public.access.modulesdaovba
'1998/11/24
'The algorithm can round a number ending with 5 thousandths
'to a number ending with an even-numbered hundredths (banker's rounding).
'e.g. if Bankers = True then Round(8.565 ,2,true) = 8.56

'--------------------
Dim dblPower As Double
Dim varTemp As Variant
Dim intSgn As Integer

If Not IsNumeric(Number) Then
' Raise an error indicating that
' you've supplied an invalid parameter.
Err.Raise 5
End If
dblPower = 10 ^ NumDigits
' Is this a negative number, or not?
' intSgn will contain -1, 0, or 1.
intSgn = Sgn(Number)
Number = Abs(Number)

' Do the major calculation.
varTemp = CDec(Number) * dblPower + 0.5

' Now round to nearest even, if necessary.
If UseBankersRounding Then
If Int(varTemp) = varTemp Then
' You could also use:
' varTemp = varTemp + (varTemp Mod 2 = 1)
' instead of the next If ...Then statement,
' but I hate counting on TRue == -1 in code.
If varTemp Mod 2 = 1 Then
varTemp = varTemp - 1
End If
End If
End If
' Finish the calculation.
Round = intSgn * Int(varTemp) / dblPower
End Function