By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,046 Members | 1,350 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,046 IT Pros & Developers. It's quick & easy.

Correctly sum a column of currency in a report -- rounding issues???

P: n/a
I have a report showing Rate, Hours and Total Pay (which is
Rate*Hours) for several workers. I want to sum the Total Pay and get
a CORRECT figure. This seems like it should be such a simple task.
But there seems to be rounding issues in Access 2000 that I can't
figure out. It seems that, even though the report is displaying Total
Pay as currency rounded off to two decimal places, the report is
actually using more than two decimal places in the summing
calculation. Can anyone help me? Thanks.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
If you have not already done so, create a query to act as the RecordSource
for your report.

In the query, enter a calculated field that rounds each line to the nearest
cent. Then the total will have no rounding issues.

Type something like this into the Field row in query design:
TotalPay: CCur(Nz(Round([Rate] * [Hours], 2)))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lynn N." <lu*******@aol.com> wrote in message
news:48**************************@posting.google.c om...
I have a report showing Rate, Hours and Total Pay (which is
Rate*Hours) for several workers. I want to sum the Total Pay and get
a CORRECT figure. This seems like it should be such a simple task.
But there seems to be rounding issues in Access 2000 that I can't
figure out. It seems that, even though the report is displaying Total
Pay as currency rounded off to two decimal places, the report is
actually using more than two decimal places in the summing
calculation. Can anyone help me? Thanks.

Nov 13 '05 #2

P: n/a
Allen, your solution didn't work for me for some reason. But I found
a function at http://www.woodyswatch.com/access/archtemplate.asp?3-22
that worked quite well. There's an article in there all about
rounding in Access.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40**********************@per-qv1-newsreader-01.iinet.net.au>...
If you have not already done so, create a query to act as the RecordSource
for your report.

In the query, enter a calculated field that rounds each line to the nearest
cent. Then the total will have no rounding issues.

Type something like this into the Field row in query design:
TotalPay: CCur(Nz(Round([Rate] * [Hours], 2)))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lynn N." <lu*******@aol.com> wrote in message
news:48**************************@posting.google.c om...
I have a report showing Rate, Hours and Total Pay (which is
Rate*Hours) for several workers. I want to sum the Total Pay and get
a CORRECT figure. This seems like it should be such a simple task.
But there seems to be rounding issues in Access 2000 that I can't
figure out. It seems that, even though the report is displaying Total
Pay as currency rounded off to two decimal places, the report is
actually using more than two decimal places in the summing
calculation. Can anyone help me? Thanks.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.