Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85

    Round up to nearest nickel

    Round(20 * [MyField], 0) / 20

    MyField = 733.20 - I want my value to be 733.25




  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks kind of odd. Usually if the value is an exact multiple of a nickel (like 733.20), usually you just want to use that value instead of adding another nickel to it.

    So if I understand you correctly then,
    733.20
    733.21
    733.22
    733.23
    733.24
    would all round up to 733.25, and
    733.25
    733.26
    733.27
    733.28
    733.29
    would all round up to 733.30.

    Do I have that right?

  4. #4
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    that would be right

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Here's one way:
    RoundedUpValue: (Int([MyField]/0.05)*0.05)+0.05

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    I didn't pick up on that Joe; good catch!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I didn't pick up on that Joe; good catch!
    Yeah, lucky for them they caught me in the afternoon and not in the morning!

  8. #8
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    This is my code - and it is working - but they want it to not round if it ends in 0 or 5

    1-4 round up - 6-9 round up - ends in 0 or 5 do not round

    (Int((IIf(Mid([WBCode],4,1)="D",([WBCost]+[WBShipping])*[ExchangeRate],IIf(Mid([WBCode],4,1)="W",([WBCost]*1.01)*[ExchangeRate],[WBCost]*[ExchangeRate])))/0.05)*0.05)+0.05

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    This is my code - and it is working - but they want it to not round if it ends in 0 or 5
    That is what I asked originally, and even gave you an example, but you said no, and confirmed that you wanted the values ending in 0 or 5 to roundup.
    So the question has changed now?
    Just want to make sure before I spend any more time on it.

  10. #10
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    And that is what they originally told me they wanted - now after I have it working they said that it should not round up for 0 and 5 - I'm sorry for the trouble - I am fairly new to this and it is frustrating when they change their minds. Thank you for helping me with this.

    0 Do not round up

    Round Up
    1
    2
    3
    4

    5 - don't round

    Round Up
    6
    7
    8
    9

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Paul gave a link to a nice UDF version in post #2 that should do that.
    If you want a formulaic answer that does not use VBA, here is one that should work:
    Code:
    RoundedUpValue: (Int([Amount]/0.05)*0.05)+IIf([Amount]/0.05=Int([Amount]/0.05),0,0.05)

  12. #12
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I'm a little confused as to how I would add that to my expression:
    USDollar: (Int((IIf(Mid([WBCode],4,1)="D",([WBCost]+[WBShipping])*[ExchangeRate],IIf(Mid([WBCode],4,1)="W",([WBCost]*1.01)*[ExchangeRate],[WBCost]*[ExchangeRate])))/0.05)*0.05)+0.05

    Would I add the info form the +IIf([Amount]/0.05=Int([Amount]/0.05),0,0.05) and put my whole expression int he Amound area twice?

  13. #13
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    Test: (Int((IIf(Mid([WBCode],4,1)="D",([WBCost]+[WBShipping])*[ExchangeRate],IIf(Mid([WBCode],4,1)="W",([WBCost]*1.01)*[ExchangeRate],[WBCost]*[ExchangeRate])))/0.05)*0.05)
    +IIf((Int((IIf(Mid([WBCode],4,1)="D",([WBCost]+[WBShipping])*[ExchangeRate],IIf(Mid([WBCode],4,1)="W",([WBCost]*1.01)*[ExchangeRate],[WBCost]*[ExchangeRate])))/0.05)=
    Int((Int((IIf(Mid([WBCode],4,1)="D",([WBCost]+[WBShipping])*[ExchangeRate],IIf(Mid([WBCode],4,1)="W",([WBCost]*1.01)*[ExchangeRate],[WBCost]*[ExchangeRate])))/0.05),0,0.5)

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I would say you have too much going on in a single expression, which makes it very hard to follow/support (too much for my liking, anyway). I don't even know what you are trying to do with the other part of it (you haven't said). I would recommend creating a User Defined Function in VBA to handle it all.

  15. #15
    Rhubie is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    85
    I don't know VBA

    There are different conditions based on their code for each product (which I am finding that with the mid function

    If it is a D in the code they add a shipping cost
    If it is a W in their code it gets multiplied be 1%
    If it is anything else it just gets multiplied by the exchange rate
    Then it gets multiplied by their exchange rate - then they want to round it up 5 cents

    That all works - I'm just not sure how to round up if it is 1,2,3, 4 and round up if it is 6,7,8,9 - not if it ends with 0 or 5

    Where would I put the VBA code in the access expression?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Round up issue?
    By Hammer in forum Access
    Replies: 3
    Last Post: 12-13-2012, 12:46 PM
  2. round up
    By Rhubie in forum Access
    Replies: 1
    Last Post: 08-29-2012, 05:14 PM
  3. Round Time in access
    By waqas in forum Access
    Replies: 1
    Last Post: 08-31-2011, 02:04 PM
  4. Help! - Going round in Circles
    By Sleepymum in forum Database Design
    Replies: 11
    Last Post: 02-01-2011, 09:15 AM
  5. Round Up/Down 4 Decimals to 2
    By newbie in forum Access
    Replies: 6
    Last Post: 10-18-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums