Problems with rounding

[Zelgrath]

I have a query that multiplies two 'single' data type
number fields. The output is usually slightly wrong. For
example I will get 2.4999999 where I was expecting 2.5. To
correct this I want to round these numbers to two decimal
places. Where is the best place to do this? I put the
round function in my query but it still shows all the
decimal places. It appears to round the number and then
the decimal places are added again. My query is this

ScaCollectInt: round([CollectiveInt]*
[DistributionFactor],2)

Without the round() | With the round
1.84800004959106 | 1.85000002384186
0.165000006556511 | 0.170000001788139

How could I simply report 1.85 and 0.17?

 

[Allen Browne]

If you want to hide the extra places, you can right-click the field in query
design, and choose Properties. Set:
Format: Fixed
Decimal Places: 2

 

[Lynn Trapp]

That is quite bizarre, but checked it out for myself and it happens to me
too. If you change the datatype to Double it will round correctly.

 

[Lynn Trapp]

Allen,
Obviously, your work around does what the OP needs, but do you know any
reason the Single datatype would react this way while Double works just
fine?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

If you want to hide the extra places, you can right-click the field in
query design, and choose Properties. Set:
Format: Fixed
Decimal Places: 2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that multiplies two 'single' data type
number fields. The output is usually slightly wrong. For
example I will get 2.4999999 where I was expecting 2.5. To
correct this I want to round these numbers to two decimal
places. Where is the best place to do this? I put the
round function in my query but it still shows all the
decimal places. It appears to round the number and then
the decimal places are added again. My query is this

ScaCollectInt: round([CollectiveInt]*
[DistributionFactor],2)

Without the round() | With the round
1.84800004959106 | 1.85000002384186
0.165000006556511 | 0.170000001788139

How could I simply report 1.85 and 0.17?

 

[Zelgrath]

Thanks for the help Allen and Lynn. Changing the type to
double does fix the rounding error in the query. Since I
posted I have added the round function to some of my
reports based on the query and it appears to work fine.
Sice I will have to add it to any calculated fields on the
reports anyway I might just do all the rounding there
instead of the query.

Thanks again
Zelgrath

 

[Allen Browne]

Any floating point number is subject to rounding errors because the binary
representation is not complete. As an analogy, you cannot store 1/3
accurately as a decimal number, because you need an endless number of
decimal places.

Here's an example of a loop that never terminates, because the 7 additions
of 1/7 does not equal 1:
Sub ShowRoundingError()
'Note: Use Ctrl+Break to stop this loop.
Dim dblValue As Double
Dim dblResult As Double
dblValue = 1 / 7
Do Until dblResult = 1 'Does not terminate!!!
dblResult = dblResult + dblValue
Debug.Print dblResult
Loop
End Sub

Similarly two date/time values that look the same may not match in a query,
due to inaccuracies beyond what is visible. This means that floating point
field types should not be used as a key value, because the fractional values
may not match correctly.

In contrast, the Currency data type is a fixed point number. For the
purposes of math, Access ignores the decimal point and treats it as an
integer. Then it pops the decimal point back in again when it displays the
result. Since the integer math is precise to the 4 places, no floating point
errors occur.

The Decimal data type attempts to do the same thing as a scalable number.
However, the implementation in Access is so bad that it cannot even perform
a basic sort on this type:
http://members.iinet.net.au/~allenbrowne/bug-08.html

Here's another example of the difference:
? Round(0.545@ , 2), Round(0.545# , 2)
0.54 0.55
The Round() function is supposed to round towards the even number - down
towards the 4 in this case. The Currency type is precise, and so rounds
correctly. The Double is just slightly high, but it's enough to throw out
the rounding and make it round up where it should be down. Similarly 0.575#
rounds down when it should go up (towards the even digit 8).

BTW, I'm not keen on the idea of applying formats to query fields. As a
developer, I don't display queries to end users, so I leave them to display
exactly what they contain. Forms and reports are where the formatting
belongs IMHO.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Obviously, your work around does what the OP needs, but do you know any
reason the Single datatype would react this way while Double works just
fine?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

If you want to hide the extra places, you can right-click the field in
query design, and choose Properties. Set:
Format: Fixed
Decimal Places: 2

I have a query that multiplies two 'single' data type
number fields. The output is usually slightly wrong. For
example I will get 2.4999999 where I was expecting 2.5. To
correct this I want to round these numbers to two decimal
places. Where is the best place to do this? I put the
round function in my query but it still shows all the
decimal places. It appears to round the number and then
the decimal places are added again. My query is this

ScaCollectInt: round([CollectiveInt]*
[DistributionFactor],2)

Without the round() | With the round
1.84800004959106 | 1.85000002384186
0.165000006556511 | 0.170000001788139

How could I simply report 1.85 and 0.17?

 

[Lynn Trapp]

Any floating point number is subject to rounding errors because the binary

representation is not complete. As an analogy, you cannot store 1/3
accurately as a decimal number, because you need an endless number of
decimal places.

Of course, the rounding error problem is there, but why would the Round()
function display the information differently for double than it does for
single? Is it because the multiplication causes the result to be implicitly
converted to double and, thus, becomes confusing to the database engine?

BTW, I'm not keen on the idea of applying formats to query fields. As a
developer, I don't display queries to end users, so I leave them to
display exactly what they contain. Forms and reports are where the
formatting belongs IMHO.

I agree with you on this one and it looks like the OP has found that out
too.

 

[Dave]

one important thing to note... if you are really working with money you
should use the Currency type, this handles all of those round off and format
things automatically and is much safer than using singles or doubles if you
are really worried about not losing .01's here and there.

Thanks for the help Allen and Lynn. Changing the type to
double does fix the rounding error in the query. Since I
posted I have added the round function to some of my
reports based on the query and it appears to work fine.
Sice I will have to add it to any calculated fields on the
reports anyway I might just do all the rounding there
instead of the query.

Thanks again
Zelgrath

-----Original Message-----
I have a query that multiplies two 'single' data type
number fields. The output is usually slightly wrong. For
example I will get 2.4999999 where I was expecting 2.5. To
correct this I want to round these numbers to two decimal
places. Where is the best place to do this? I put the
round function in my query but it still shows all the
decimal places. It appears to round the number and then
the decimal places are added again. My query is this

ScaCollectInt: round([CollectiveInt]*
[DistributionFactor],2)

Without the round() | With the round
1.84800004959106 | 1.85000002384186
0.165000006556511 | 0.170000001788139

How could I simply report 1.85 and 0.17?
.

 

[Allen Browne]

Yes, the number is rounded, but it is still a floating point number, and JET
4 frequently gets the data types muddled. I've talked to MS Support about
this, but they can't/won't address the issue, and didn't think much of my
attempts to use TypeName() to figure out how JET understands the data.

OTOH, explicitly typecasting does help to get JET to use the right type, so
typecasting after rounding might make the difference.

 

[Lynn Trapp]

Yes, the number is rounded, but it is still a floating point number, and

JET 4 frequently gets the data types muddled.

Ah, now that might be an understatement. ;-)

I've talked to MS Support about this, but they can't/won't address the
issue,

Could the problem be entirely with how the CPU handles floating point
numbers? If so, then it may be more of MS "can't" address the issue.

and didn't think much of my attempts to use TypeName() to figure out how
JET understands the data.

Spoil sports... <g>

 

[Allen Browne]

JET's problems are much bigger than the floating-point math issue. The
underlying cause is the Microsoft tries to hide the whole issue of data
types from users.

As a result, JET regularly gives wrong answers, e.g. it:
- treats numeric values as string data,
- gets thoroughtly confused about dates,
- misapplies undeclared parameters in a query,
- misunderstands unbound controls on a form,
- assumes a calculated field is string if there are nulls in the first few
rows.

I've faced this so many times, that I now never create a calculated query
column without explicitly typecasting the result. CVDate() handles nulls
correctly, but the other VBA types don't so you actually lose the ability to
have a null in a calculated column. That's a pretty severe restriction to
have to live with, but it's better than the alternative (completely wrong
calculations).

More info:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html