Round up to nearest nickel

[Rhubie]

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



MyField = 733.20 - I want my value to be 733.25

[pbaldy]

This should help:

http://support.microsoft.com/kb/209996

[JoeM]

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?

[Rhubie]

that would be right

[JoeM]

Here's one way:
RoundedUpValue: (Int([MyField]/0.05)*0.05)+0.05

[pbaldy]

I didn't pick up on that Joe; good catch!

[JoeM]

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!

[Rhubie]

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

[JoeM]

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.

[Rhubie]

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

[JoeM]

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:

RoundedUpValue: (Int([Amount]/0.05)*0.05)+IIf([Amount]/0.05=Int([Amount]/0.05),0,0.05)

[Rhubie]

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?

[Rhubie]

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)

[JoeM]

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.

[Rhubie]

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?