Rounding problem

[Guest]

I am off a cent on some report lines. When I try to fix it (using int or fix
functions) I am off much larger amounts.

I am trying to subtract the commission from the payment owed:
Payment - txtCommission = Amount Still Owed

Examples:
Payment txtCommission Amount Still Owed
$32.18 $32.18 ($0.01) WRONG
$170.50 $170.50 $0.00 CORRECT

txtCommission control source is a report textbox named TotalAmount, times
5%
=[TotalAmount]*0.05

Payment control source is a field in the report source query
InvoiceCreditAmt

Amount Still Owed control source is:

=[txtCommission]-[InvoiceCreditAmt]

These 3 report textboxes have Format set to Currency and Decimal Places set
to 2.
The 2 amount fields in the database use DataType: Number, Field Size:
Single, Format: Currency

Can anyone tell me the correct source for these textboxes so none of them
have rounding errors? I would think since I'm using currency that this would
work.

Thanks in advance!
Pam

 

[weathermanfsu]

Use this module if you are doing your rounding in code

Function RoundAU(X As Control)

X = Int(X * 100 + 0.5) / 100

End Function

Use this if you are rounding in a report...

Function RoundCC(X)

RoundCC = Int(X * 100 + 0.5) / 100

End Function

-David

 

[Marshall Barton]

I am off a cent on some report lines. When I try to fix it (using int or fix
functions) I am off much larger amounts.

I am trying to subtract the commission from the payment owed:
Payment - txtCommission = Amount Still Owed

Examples:
Payment txtCommission Amount Still Owed
$32.18 $32.18 ($0.01) WRONG
$170.50 $170.50 $0.00 CORRECT

txtCommission control source is a report textbox named TotalAmount, times
5%
=[TotalAmount]*0.05

Payment control source is a field in the report source query
InvoiceCreditAmt

Amount Still Owed control source is:

=[txtCommission]-[InvoiceCreditAmt]

These 3 report textboxes have Format set to Currency and Decimal Places set
to 2.
The 2 amount fields in the database use DataType: Number, Field Size:
Single, Format: Currency

Can anyone tell me the correct source for these textboxes so none of them
have rounding errors? I would think since I'm using currency that this would
work.

I think your problem is that the fields in the table are of
type Single when you really want them to be type Currency.

If you can not change the inappropriate field type in the
table, then try converting them in the report's RecordSource
query:

SELECT CCur(InvoiceCreditAmt) As XInvoiceCreditAmt,
CCur(TotalAmount) As XTotalAmount,
. . .

 

[weathermanfsu]

I oo have faced this problem. The fields in the table were type
currency but when calculations were done they did not round correctly.
That is why I am using the formulas I posted. Now they work correctly.

I am off a cent on some report lines. When I try to fix it (using int or fix
functions) I am off much larger amounts.

I am trying to subtract the commission from the payment owed:
Payment - txtCommission = Amount Still Owed

Examples:
Payment txtCommission Amount Still Owed
$32.18 $32.18 ($0.01) WRONG
$170.50 $170.50 $0.00 CORRECT

txtCommission control source is a report textbox named TotalAmount, times
5%
=[TotalAmount]*0.05

Payment control source is a field in the report source query
InvoiceCreditAmt

Amount Still Owed control source is:

=[txtCommission]-[InvoiceCreditAmt]

These 3 report textboxes have Format set to Currency and Decimal Places set
to 2.
The 2 amount fields in the database use DataType: Number, Field Size:
Single, Format: Currency

Can anyone tell me the correct source for these textboxes so none of them
have rounding errors? I would think since I'm using currency that this would
work.

I think your problem is that the fields in the table are of
type Single when you really want them to be type Currency.

If you can not change the inappropriate field type in the
table, then try converting them in the report's RecordSource
query:

SELECT CCur(InvoiceCreditAmt) As XInvoiceCreditAmt,
CCur(TotalAmount) As XTotalAmount,
. . .

 

[Marshall Barton]

I am certain that your Currency numbers rounded correctly.
However, a rounding function also truncates the numbers to
two places, which is most likely your real point about
Sum(Round(x)) not being the same as Round(Sum(x))

Even if the numbers were originally 2 places, multiplying by
..05 might result in 4 places so an intermediate rounding to
two places is appropriate (regardless of the type of
rounding that's used, yours or the builtin Round function).

My suggested query quick fix should have been:
SELECT Round(CCur(InvoiceCreditAmt),2) As XInvoiceCreditAmt,
Round(CCur(TotalAmount),2) As XTotalAmount,
. . .
and the commission calculation should be:
=Round([TotalAmount]*0.05, 2)

Thanks for pointing out this subtle issue.
--
Marsh
MVP [MS Access]

I oo have faced this problem. The fields in the table were type
currency but when calculations were done they did not round correctly.
That is why I am using the formulas I posted. Now they work correctly.

I am off a cent on some report lines. When I try to fix it (using int or fix
functions) I am off much larger amounts.

I am trying to subtract the commission from the payment owed:
Payment - txtCommission = Amount Still Owed

Examples:
Payment txtCommission Amount Still Owed
$32.18 $32.18 ($0.01) WRONG
$170.50 $170.50 $0.00 CORRECT

txtCommission control source is a report textbox named TotalAmount, times
5%
=[TotalAmount]*0.05

Payment control source is a field in the report source query
InvoiceCreditAmt

Amount Still Owed control source is:

=[txtCommission]-[InvoiceCreditAmt]

These 3 report textboxes have Format set to Currency and Decimal Places set
to 2.
The 2 amount fields in the database use DataType: Number, Field Size:
Single, Format: Currency

Can anyone tell me the correct source for these textboxes so none of them
have rounding errors? I would think since I'm using currency that this would
work.

I think your problem is that the fields in the table are of
type Single when you really want them to be type Currency.

If you can not change the inappropriate field type in the
table, then try converting them in the report's RecordSource
query:

SELECT CCur(InvoiceCreditAmt) As XInvoiceCreditAmt,
CCur(TotalAmount) As XTotalAmount,
. . .

 

[Guest]

Marshall, David, (and anyone else who wants to help)

I still have a rounding problem.

I took your suggestions and made the following changes:

1. Changed any table amounts to type Currency instead of Number, Single,
Currency.
The report text boxes still use type Currency with Decimal Places set to 2.

2. I found it difficult to change the query, so instead I changed the
Source for fields in the report.

InvoiceCreditAmt has Visible = No, Source is InvoiceCreditAmt
TotalAmount has Visible = No, Source is TotalAmount
txtCommission Visible = Yes, Source is =Round([TotalAmount]*0.05,2)
(I also tried changing txtCommission source to
=Round(CCur([TotalAmount])*.05,2)

I added 2 fields:
XTotalAmount Visible=Yes, Source is =Round(CCur([TotalAmount]),2)
XInvoiceCreditAmt Visible=Yes, Source is =Round(CCur([InvoiceCreditAmt]),2)

Amount Still Owed has source =[txtCommission]-[InvoiceCreditAmt] and I also
tried =[txtCommission]-[XInvoiceCreditAmt] with the same result.

The result is that it fixed some but not all of the rounding errors, and
added new rounding errors. Some balances show as $0.00 and others still
show as ($0.01)

I looked at the tables and they appear correct, using currency and 2 decimal
places and accurate amounts.

Here's the data showing in the report:
Invoice Amt Commission Payment Amount Still Owed
$643.50 $32.18 $32.18 $0.00 (now corrected)
$650.10 $32.51 $32.51 ($0.01) (still not correct)
$742.50 $37.12 $37.13 ($0.01) (now shows wrong
commission)

So, more guidance is needed, and appreciated.

I also now have another problem... I include all invoice records even if
they were NOT paid, which now show Payment and Amount Still Owed fields as
#Error. How do I tell the report (or probably the query) to put 0 in the
Payment field if there is no payment record?

Thanks in advance,
Pam

 

[Guest]

Marshall, David, (and anyone else who wants to help)

I still have a rounding problem.

I took your suggestions and made the following changes:

1. Changed any table amounts to type Currency instead of Number, Single,
Currency.
The report text boxes still use type Currency with Decimal Places set to 2.

2. I found it difficult to change the query, so instead I changed the
Source for fields in the report.

InvoiceCreditAmt has Visible = No, Source is InvoiceCreditAmt
TotalAmount has Visible = No, Source is TotalAmount
txtCommission Visible = Yes, Source is =Round([TotalAmount]*0.05,2)
(I also tried changing txtCommission source to
=Round(CCur([TotalAmount])*.05,2)

I added 2 fields:
XTotalAmount Visible=Yes, Source is =Round(CCur([TotalAmount]),2)
XInvoiceCreditAmt Visible=Yes, Source is =Round(CCur([InvoiceCreditAmt]),2)

Amount Still Owed has source =[txtCommission]-[InvoiceCreditAmt] and I also
tried =[txtCommission]-[XInvoiceCreditAmt] with the same result.

The result is that it fixed some but not all of the rounding errors, and
added new rounding errors. Some balances show as $0.00 and others still
show as ($0.01)

I looked at the tables and they appear correct, using currency and 2 decimal
places and accurate amounts.

Here's the data showing in the report:
Invoice Amt Commission Payment Amount Still Owed
$643.50 $32.18 $32.18 $0.00 (now corrected)
$650.10 $32.51 $32.51 ($0.01) (still not correct)
$742.50 $37.12 $37.13 ($0.01) (now shows wrong
commission)

So, more guidance is needed, and appreciated.

I also now have another problem... I include all invoice records even if
they were NOT paid, which now show Payment and Amount Still Owed fields as
#Error. How do I tell the report (or probably the query) to put 0 in the
Payment field if there is no payment record?

Thanks in advance,
Pam

 

[Marshall Barton]

I still have a rounding problem.

I took your suggestions and made the following changes:

1. Changed any table amounts to type Currency instead of Number, Single,
Currency.
The report text boxes still use type Currency with Decimal Places set to 2.

2. I found it difficult to change the query, so instead I changed the
Source for fields in the report.

InvoiceCreditAmt has Visible = No, Source is InvoiceCreditAmt
TotalAmount has Visible = No, Source is TotalAmount
txtCommission Visible = Yes, Source is =Round([TotalAmount]*0.05,2)
(I also tried changing txtCommission source to
=Round(CCur([TotalAmount])*.05,2)

I added 2 fields:
XTotalAmount Visible=Yes, Source is =Round(CCur([TotalAmount]),2)
XInvoiceCreditAmt Visible=Yes, Source is =Round(CCur([InvoiceCreditAmt]),2)

Amount Still Owed has source =[txtCommission]-[InvoiceCreditAmt] and I also
tried =[txtCommission]-[XInvoiceCreditAmt] with the same result.

The result is that it fixed some but not all of the rounding errors, and
added new rounding errors. Some balances show as $0.00 and others still
show as ($0.01)

I looked at the tables and they appear correct, using currency and 2 decimal
places and accurate amounts.

Here's the data showing in the report:
Invoice Amt Commission Payment Amount Still Owed
$643.50 $32.18 $32.18 $0.00 (now corrected)
$650.10 $32.51 $32.51 ($0.01) (still not correct)
$742.50 $37.12 $37.13 ($0.01) (now shows wrong
commission)

So, more guidance is needed, and appreciated.

I also now have another problem... I include all invoice records even if
they were NOT paid, which now show Payment and Amount Still Owed fields as
#Error. How do I tell the report (or probably the query) to put 0 in the
Payment field if there is no payment record?

txtCommission is working on an unrounded value. Try
changing txtCommission to:
=Round([XTotalAmount]*0.05,2)

I am somewhat unsure about what other changes you made. It
seems like you are confusing the data type and format of
your table field and control values. The Format and Decimal
places are pretty much irrelevant, it's the Data Type of the
table field that is critical.

Your are probably getting the #Error because the credit
amount is Null. Try changing XInvoiceCreditAmt to:
=Round(CCur(Nz([InvoiceCreditAmt], 0)),2)

 

[Guest]

The above was a duplicate post. See previous posts above these two. Sorry.

Pam

 

[Guest]

The above help I received was excellent, fixing most of the problems, but not
quite everything. I had to add .001 to commission to fix the final rounding
up problem. Now everything works. The following summarizes what worked for
anyone searching for future help on rounding, rounding up, round up, rounding
errors, round, round in reports, or report rounding.

Commission Due is 5 % of the invoice amount.
Commission Due - Payment Received = Balance Due
I noticed that Payment was always rounded up.

Invoice Amount, textbox XTotalAmount, control source
=Round(CCur([TotalAmount]),2)

Commission Due, textbox txtCommission, control source
=Round(([XTotalAmount]+0.001)*0.05,2) Notice that I needed to add .001 to
have it always round up at .05

Payment Received, textbox XInvoiceCreditAmt, control source
=Round(CCur(Nz([InvoiceCreditAmt],0)),2)

Balance Due, textbox txtBalance, control source
=[txtCommission]-[XInvoiceCreditAmt]

Don't forget to set amounts in the tables to datatype currency.
Don't forget that an amount in brackets [ ] is a report textbox, not table
field, and therefore a hidden textbox was created with Visible = No for
TotalAmount and InvoiceCreditAmt.

Thanks for the excellent and fast help of Marshall Barton, MVP and David
alias weathermanfsu.

 

[Marshall Barton]

Pam, the reason you added the .001 is that you want .005 to
always round up. The Round function rounds .005 to the
nearest even number is the next position (e.g. .135 and .145
both round to .14). This is called Bankers Rounding and is
actually a little more accurate over a column of numbers
than Always Up rounding.

To get what you wanted, you could have used David's RoundAU
function instead of Access's Round function with the funky
..001