Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I replace a value generated in a calculated control.

Discussion in 'Information Technology' started by Adam, Jul 28, 2006.

  1. Adam

    Adam
    Expand Collapse
    Guest

    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)
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <Adam@discussions.microsoft.com> wrote in message
    news:AA4C752E-55C0-4D02-8D28-995B04E20AC3@microsoft.com...
    >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)
    >
    >
     
  4. Adam

    Adam
    Expand Collapse
    Guest

    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" <Adam@discussions.microsoft.com> wrote in message
    > news:AA4C752E-55C0-4D02-8D28-995B04E20AC3@microsoft.com...
    > >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)
    > >
    > >

    >
    >
    >
     

Share This Page