Sum in Report is off by a penny!

[aagarcia]

My sum from my a report footer total at the end of a report is off by
a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85

[Bruce M. Thompson]

> My sum from my a report footer total at the end of a report is off by

a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85

See the following technical paper by Luke Chung - it may help explain why
different approaches to calculations in Access sometimes come up with different
answers:

When Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Michael \(michka\) Kaplan [MS]]

Although for every truth in that paper there is something less than true,
and Luke does tend to not talk about the mistakes that he made that others
pointed out (the fact that he did not even pay attention to CDec, for
example) or his decision to not push for a fix (an intrinsic Decimal
datatype in VBA) when he had a good oportunity.

Every time I see this paper all I can do is sigh. Consulting must have been
too lucrative. :-(

MichKa

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vq************@corp.supernews.com...

My sum from my a report footer total at the end of a report is off by
a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85
See the following technical paper by Luke Chung - it may help explain why
different approaches to calculations in Access sometimes come up with

different answers:

When Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[aagarcia]

So you have to buy the book to get the answer. (i smell a rat) I
cannot believe that all the versions have never been fixed. It makes
access worthless to its users when you can't perform a basic
calculation.

"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in message news:<3f********@news.microsoft.com>...

Although for every truth in that paper there is something less than true,
and Luke does tend to not talk about the mistakes that he made that others
pointed out (the fact that he did not even pay attention to CDec, for
example) or his decision to not push for a fix (an intrinsic Decimal
datatype in VBA) when he had a good oportunity.

Every time I see this paper all I can do is sigh. Consulting must have been
too lucrative. :-(

MichKa

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vq************@corp.supernews.com...

My sum from my a report footer total at the end of a report is off by
a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85

See the following technical paper by Luke Chung - it may help explain why
different approaches to calculations in Access sometimes come up with

different

answers:

When Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

> NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Bruce M. Thompson]

> Although for every truth in that paper there is something less than true,

and Luke does tend to not talk about the mistakes that he made that others
pointed out (the fact that he did not even pay attention to CDec, for
example) or his decision to not push for a fix (an intrinsic Decimal
datatype in VBA) when he had a good oportunity.

Thanks, MichKa. I hadn't really thought about that, unfortunately.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Michael \(michka\) Kaplan [MS]]

Who says you need to buy a book?

There may well have been other, less selfish reasons that were not
communicated to me, but Luke did tell me at a face-to-face meeting that it
"was not his job to fix Access bugs" and that is was Microsoft's job. He was
not convinced by my plea to make the case on the basis of the many customers
FMS had serviced with issues and did not want to pursue the issue.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

"aagarcia" <pd*******@satx.rr.com> wrote in message
news:70*************************@posting.google.co m...

So you have to buy the book to get the answer. (i smell a rat) I
cannot believe that all the versions have never been fixed. It makes
access worthless to its users when you can't perform a basic
calculation.

"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in

message news:<3f********@news.microsoft.com>...

Although for every truth in that paper there is something less than true, and Luke does tend to not talk about the mistakes that he made that others pointed out (the fact that he did not even pay attention to CDec, for
example) or his decision to not push for a fix (an intrinsic Decimal
datatype in VBA) when he had a good oportunity.

Every time I see this paper all I can do is sigh. Consulting must have been too lucrative. :-(

MichKa

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vq************@corp.supernews.com...

> My sum from my a report footer total at the end of a report is off by > a penny! The data totals coming from query are correct. Only the
> calculations (=Sum([Total])doesn't add the penny. I've tried changing > the format and changing the decimal places but its not working. hourly > wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
> 138.57 total should be 979.86 but the total from sum total says
> $979.85

See the following technical paper by Luke Chung - it may help explain why different approaches to calculations in Access sometimes come up with

different

answers:

When Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
>> NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<

[Mike MacSween]

Just to piggy back on this thread...

This expression (in a report):

=Format(Sum(([Score]*[PercentOfGrade])*([ModuleCats]/[TotalCats])/2),"0.00")

Occasionally produces differences of 1/000th in Access XP compared to what
it does in Access 97. Not very often though.

e.g. 82.45 (97) becomes 82.44 (in XP).

The difference (so far) seems to only ever be 1/100th and always down (from
97 to XP).

Score and ModuleCats are byte numbers, TotalCats is an integer,
PercentOfGrade is a double and is always 1 or < 1. PercentOfGrade is usually
to one decimal place. But occasionally to 4 decimal places. (i.e. usually .5
or .2, sometimes 0.0625)

Any ideas folks? I'm assuming this is some change to how Access does
rounding. But the other change to the system is from NT clients to XP
clients.

Cheers, Mike

"aagarcia" <pd*******@satx.rr.com> wrote in message
news:70**************************@posting.google.c om...

My sum from my a report footer total at the end of a report is off by
a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85

[Mike MacSween]

Well, just done some investigating. This was my 97 expression:

=Format(Sum(([Score]*[PercentOfGrade])*([ModuleCats]/[TotalCats])/2),"0.00")

on converting to XP I got

=Format(Sum(([Score]*[PercentOfGrade])*([ModuleCats]/[TotalCats])/2),"Fixed"
)

Nice of Access to do that without asking. Why? Anything else it might have
changed behind my back? The back end data file is still in Access 97 format
by the way, this is just the front end that's been converted.

Mike
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

Just to piggy back on this thread...

This expression (in a report):

=Format(Sum(([Score]*[PercentOfGrade])*([ModuleCats]/[TotalCats])/2),"0.00")
Occasionally produces differences of 1/000th in Access XP compared to what
it does in Access 97. Not very often though.

e.g. 82.45 (97) becomes 82.44 (in XP).

The difference (so far) seems to only ever be 1/100th and always down (from 97 to XP).

Score and ModuleCats are byte numbers, TotalCats is an integer,
PercentOfGrade is a double and is always 1 or < 1. PercentOfGrade is usually to one decimal place. But occasionally to 4 decimal places. (i.e. usually ..5 or .2, sometimes 0.0625)

Any ideas folks? I'm assuming this is some change to how Access does
rounding. But the other change to the system is from NT clients to XP
clients.

Cheers, Mike

"aagarcia" <pd*******@satx.rr.com> wrote in message
news:70**************************@posting.google.c om...

My sum from my a report footer total at the end of a report is off by
a penny! The data totals coming from query are correct. Only the
calculations (=Sum([Total])doesn't add the penny. I've tried changing
the format and changing the decimal places but its not working. hourly
wage is 39.59. employee hour total: 316.72, 108.87, 277.13, 138.57,
138.57 total should be 979.86 but the total from sum total says
$979.85