Welcome to SPN

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

Sign Up Now!

CAN ACCESS ROUNDUP?

Discussion in 'Information Technology' started by need help with rounding in access, Jul 28, 2006.

Tags:
  1. need help with rounding in access

    Guest

    In need to roundup a simple markup formula for pricing. Does Access permit
    the rounding up to the nearest $10?
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Yes, but you have to write the function yourself.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "need help with rounding in access" <need help with rounding in
    access@discussions.microsoft.com> wrote in message
    news:14A6C603-75CA-4FDB-BFCD-07E9C866F86D@microsoft.com...
    > In need to roundup a simple markup formula for pricing. Does Access
    > permit
    > the rounding up to the nearest $10?
     
  4. Jerry Porter

    Jerry Porter
    Expand Collapse
    Guest

    I think the following will work:

    Int(MyAmount/10 + .99) * 10

    Replace "MyAmount" by your field.

    It divides by 10 so the rounding will happen in the 10's place. Then
    multiplies by 10 to restore the amount. The Int functions always rounds
    down, but since .99 was added first, it rounds down to the higher
    number.

    Jerry
     
  5. jwr

    jwr
    Expand Collapse
    Guest

    I have a similar problem.

    I have created a report from a query. In the query, I have the following
    format:

    Essett Payment: [PaymentAmount]*0.95

    When I create the report, I want a total of the Essett Payment. If, for
    instance, I have 8 plus 9, the report puts 16 instead of 17 for the total.
    On the report, there is a Payment Amount and essett payment. The formula I
    have for the total of Essett payments is:

    =Sum(Round([Essett Payment],2))

    If I just enter = Sum ([Essett Payment]), it is still one penny short. I
    have also tried =Round(Sum([Essett Payment]),2) and it does not work.

    Any suggestions?
    JR



    "Jerry Porter" <jerryp@personablepc.com> wrote in message
    news:1148587740.367808.53760@g10g2000cwb.googlegroups.com...
    >I think the following will work:
    >
    > Int(MyAmount/10 + .99) * 10
    >
    > Replace "MyAmount" by your field.
    >
    > It divides by 10 so the rounding will happen in the 10's place. Then
    > multiplies by 10 to restore the amount. The Int functions always rounds
    > down, but since .99 was added first, it rounds down to the higher
    > number.
    >
    > Jerry
    >
     
  6. Jerry Porter

    Jerry Porter
    Expand Collapse
    Guest

    I don't know if you're talking dollars or cents. I'm thinking it should
    be cents, since you're rounding to the nearest penny. So your total
    Essett Payment is (.08 + .09) * .95 = .1615
    And you want that to round up to .17. Is that right?

    Try:
    Round(Sum([Essett Payment] + .005 ),2)
     
  7. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    need help with rounding in access wrote:
    > In need to roundup a simple markup formula for pricing. Does Access permit
    > the rounding up to the nearest $10?


    In:

    http://groups.google.com/group/microsoft.public.access/msg/d1b5b764da495d5e

    Van T. Dinh
    MVP (Access)

    states:

    You can use:

    - Int( - [YourNumber] )


    e.g.:


    ?-Int(-3.2)
    4

    That was a very clever idea. Graphing Int(x), flipping everything
    across the Y axis (Right/Left) then flipping everything across the X
    axis (Up/Down) shows that that ceiling function is good for all values of X.

    Now, adjust the function so that it rounds up to the nearest 10:

    -10 * Int(-X / 10#)

    The '/ 10#' scales X up by a factor of 10 since 10 units in the new
    coordinate system corresponds to 1 unit in the old coordinate system.
    The '10 *' scales Y up by a factor of 10 since on the other side of the
    equation it looked like Y/10.

    Sample calculations:

    -10 * Int(-32 / 10#) = -10 * 4 = 40 (32 -> 40)
    -10 * Int(--5 / 10#) = -10 * 0 = 0 (-5 -> 0)
    -10 * Int(--12 / 10#) = -10 * 1 = -10 (-12 -> -10)
    -10 * Int(CCur(12) / 10#) = -10 * 1 = -1 (-$12 -> -10)

    Perhaps throw in an NZ() around the field name to guard against Null
    field values.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  8. jwr

    jwr
    Expand Collapse
    Guest

    That worked ! Thank you.
    "Jerry Porter" <jerryp@personablepc.com> wrote in message
    news:1148596975.540562.135970@i39g2000cwa.googlegroups.com...
    >I don't know if you're talking dollars or cents. I'm thinking it should
    > be cents, since you're rounding to the nearest penny. So your total
    > Essett Payment is (.08 + .09) * .95 = .1615
    > And you want that to round up to .17. Is that right?
    >
    > Try:
    > Round(Sum([Essett Payment] + .005 ),2)
    >
     

Share This Page