Totals of currency amounts (rounded)

[Rose B]

I have an report (invoice) that totals the lines, which have a currency
amount field. The total is taking the unrounded values and so can be a few
pence different from the total obtained by 'sight'. This is also happening in
a DSUM being run against the data in VB. Is it possible to total rounded
vaues rather than unrounded values?

 

[John W. Vinson]

I have an report (invoice) that totals the lines, which have a currency
amount field. The total is taking the unrounded values and so can be a few
pence different from the total obtained by 'sight'. This is also happening in
a DSUM being run against the data in VB. Is it possible to total rounded
vaues rather than unrounded values?

Sure. Just include a call to the Round() function in the query. For instance
if you have a table record showing 3.28 (which actually CONTAINS 3.2764) you
can base the invoice report on a Query with a calculated field

ShowAmount: Round([amount], 2)

Better, calculate (and store, if appropriate) the value rounded in the first
place. You don't say where the rounded values are coming from but perhaps they
should be rounded at the outset!

 

[Rose B]

Thanks John - I will give some further thought as to which route to follow,
but I think you may be right and it would be better to store the rounded
amount in the first place - would save all later confusion!


Thanks so much for your help - you and your colleagues who help us novices
out are wonderful!!!

I have an report (invoice) that totals the lines, which have a currency
amount field. The total is taking the unrounded values and so can be a few
pence different from the total obtained by 'sight'. This is also happening in
a DSUM being run against the data in VB. Is it possible to total rounded
vaues rather than unrounded values?

Sure. Just include a call to the Round() function in the query. For instance
if you have a table record showing 3.28 (which actually CONTAINS 3.2764) you
can base the invoice report on a Query with a calculated field

ShowAmount: Round([amount], 2)

Better, calculate (and store, if appropriate) the value rounded in the first
place. You don't say where the rounded values are coming from but perhaps they
should be rounded at the outset!