Round(20 * [MyField], 0) / 20
MyField = 733.20 - I want my value to be 733.25
Round(20 * [MyField], 0) / 20
MyField = 733.20 - I want my value to be 733.25
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?
that would be right
Here's one way:
RoundedUpValue: (Int([MyField]/0.05)*0.05)+0.05
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!I didn't pick up on that Joe; good catch!![]()
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
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.This is my code - and it is working - but they want it to not round if it ends in 0 or 5
So the question has changed now?
Just want to make sure before I spend any more time on it.
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
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)
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?
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)
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.
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?