format/decimal places aggravation

[jlute]

My query returns the math properly however the report doesn't. For
example:

query:
Field1
8.78 * 0.0937500000012 = 0.823125000010536
Field2
(0.823125000010536 * 0.1) + 0.823125000010536 = 0.90543750001159
Field3
0.90543750001159 * 1512 = 1369.02150001752

report fields are Fixed to 4 Decimal points:
Field1 displays 0.8231
Field2 displays 0.9054
Field3 displays 1369.0215

This makes sense however 0.9054 * 1512 = 1368.9648.

As the designer I understand what's going on however people who view
the report will not understand why things add up to 1369.0215 instead
of 1368.9648.

The fields in the query are NOT formatted and their decimals are set
to Auto. I've tried every format trick I know but can't iron this out.

Does anyone have any suggestions?

Thanks a bunch!

 

[Marshall Barton]

My query returns the math properly however the report doesn't. For
example:

query:
Field1
8.78 * 0.0937500000012 = 0.823125000010536
Field2
(0.823125000010536 * 0.1) + 0.823125000010536 = 0.90543750001159
Field3
0.90543750001159 * 1512 = 1369.02150001752

report fields are Fixed to 4 Decimal points:
Field1 displays 0.8231
Field2 displays 0.9054
Field3 displays 1369.0215

This makes sense however 0.9054 * 1512 = 1368.9648.

As the designer I understand what's going on however people who view
the report will not understand why things add up to 1369.0215 instead
of 1368.9648.

The fields in the query are NOT formatted and their decimals are set
to Auto. I've tried every format trick I know but can't iron this out.

The problem is that you are manually performing operations
on the displayed (rounded) values while Access is operating
on the far more accurate real values. If your users have
trouble dealing with that, then I suggest that you display
the full, unrounded values.

The only other thing you can do is perform the calculations
on the (inaccurate) rounded values so the displayed values
are the same as the calculated values:

Field2: (Round(0.823125000010536,4) * 0.1) + ...
Field3: RoundField2,4) * 1512

 

[John Spencer]

0.90543750001159 * 1512 = 1369.02150001752

CCur(0.90543750001159) * 1512 = 1368.9648

If you want th first result then you are going to need to add a note about
display rounding to your report. I wasn't sure which value you wanted to
display - 1369.0215 or 1368.9648



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[jlute]

My query returns the math properly however the report doesn't. For
example:

query:
Field1
8.78 * 0.0937500000012 = 0.823125000010536
Field2
(0.823125000010536 * 0.1) + 0.823125000010536 = 0.90543750001159
Field3
0.90543750001159 * 1512 = 1369.02150001752

report fields are Fixed to 4 Decimal points:
Field1 displays 0.8231
Field2 displays 0.9054
Field3 displays 1369.0215

This makes sense however 0.9054 * 1512 = 1368.9648.

As the designer I understand what's going on however people who view
the report will not understand why things add up to 1369.0215 instead
of 1368.9648.

The fields in the query are NOT formatted and their decimals are set
to Auto. I've tried every format trick I know but can't iron this out.

The problem is that you are manually performing operations
on the displayed (rounded) values while Access is operating
on the far more accurate real values.  If your users have
trouble dealing with that, then I suggest that you display
the full, unrounded values.

The only other thing you can do is perform the calculations
on the (inaccurate) rounded values so the displayed values
are the same as the calculated values:

Field2: (Round(0.823125000010536,4) * 0.1) + ...
Field3: RoundField2,4) * 1512

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

DANG!!! I did NOT think of that! AAAARRRRRRRRGGGGGGGHHHHHHHHHHH! I was
over-thinking the problem way too much.

You saved the day, Marsh! I HAD to employ this method because people
would CONSTANTLY question it and then look down their noses with the
attitude, "Just fix it!"

Thanks, again!

 

[jlute]

Thanks, John! This is good counsel, too but I already took Marsh's
advice. I may re-visit the CCur method again. I tried it previously
but it didn't behave as expected.