I have a calculated control generating a value of 2 other controls. My
problem is that on occasions the result is an undesirable number because of
rounding.
E.g. I multiply a subtotal field by a percentage to get a tax amount and add
these together to get a grand total, then subtract any deposits or payments
made to provide an amount outstanding. Sometimes i get a $-0.01 or $0.01 that
i dont want because of rounding. How can i replace this with $0.00 whenever
the calculation generates these undesirable numbers.
=[subtotal]*12.5/100...(this equals my tax amount)
then =[subtotal]+[taxamount] ...(get my grand total)
then =[grandtotal]-[paymentsmade]...(gives me my amount outstanding)
So i enter a payment and the result in the [amountoutstanding] calculated
control can end up being $0.01 even thought the payment amount matched the
shown amount outstanding. (because of rounding)

How about treating it as zero if it is less than (say) +/- 2 cents:
=IIf(Abs([grandtotal]-[paymentsmade]) < 0.02, 0,
[grandtotal]-[paymentsmade])

--
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.

"Adam" <(E-Mail Removed)> wrote in message
news:AA4C752E-55C0-4D02-8D28-(E-Mail Removed)...
>I have a calculated control generating a value of 2 other controls. My
> problem is that on occasions the result is an undesirable number because
> of
> rounding.
> E.g. I multiply a subtotal field by a percentage to get a tax amount and
> add
> these together to get a grand total, then subtract any deposits or
> payments
> made to provide an amount outstanding. Sometimes i get a $-0.01 or $0.01
> that
> i dont want because of rounding. How can i replace this with $0.00
> whenever
> the calculation generates these undesirable numbers.
> =[subtotal]*12.5/100...(this equals my tax amount)
> then =[subtotal]+[taxamount] ...(get my grand total)
> then =[grandtotal]-[paymentsmade]...(gives me my amount outstanding)
> So i enter a payment and the result in the [amountoutstanding] calculated
> control can end up being $0.01 even thought the payment amount matched the
> shown amount outstanding. (because of rounding)
>
>

Thank you Allen I'll give that a try...most fustrating
Again...thank you very much.


"Allen Browne" wrote:

> How about treating it as zero if it is less than (say) +/- 2 cents:
> =IIf(Abs([grandtotal]-[paymentsmade]) < 0.02, 0,
> [grandtotal]-[paymentsmade])
>
> --
> 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.
>
> "Adam" <(E-Mail Removed)> wrote in message
> news:AA4C752E-55C0-4D02-8D28-(E-Mail Removed)...
> >I have a calculated control generating a value of 2 other controls. My
> > problem is that on occasions the result is an undesirable number because
> > of
> > rounding.
> > E.g. I multiply a subtotal field by a percentage to get a tax amount and
> > add
> > these together to get a grand total, then subtract any deposits or
> > payments
> > made to provide an amount outstanding. Sometimes i get a $-0.01 or $0.01
> > that
> > i dont want because of rounding. How can i replace this with $0.00
> > whenever
> > the calculation generates these undesirable numbers.
> > =[subtotal]*12.5/100...(this equals my tax amount)
> > then =[subtotal]+[taxamount] ...(get my grand total)
> > then =[grandtotal]-[paymentsmade]...(gives me my amount outstanding)
> > So i enter a payment and the result in the [amountoutstanding] calculated
> > control can end up being $0.01 even thought the payment amount matched the
> > shown amount outstanding. (because of rounding)
> >
> >
>
>
>