Round - UP - To Nearest nickel

[razorking]

This has me stumped so far - I need to round up to the nearest nickel, preferably in a query. Have figured out how to round to the nearest nickel, but cannot get it to round up.

I round to the nearest nickel like this:
RoundedNumber: Round(20*[MyField],0)/20

Any ideas?

 

[John Big Booty]

Perhaps the article here will give you some pointers.

 

[razorking]

That is where i got the information for rounding to the nearest nickel - however I don't see how to round up to the nearest nickel.

 

[John Big Booty]

You could use the following to create an expression, in your query, to return the value rounded up to the next five cents;

 IIf([MyField]-Round(20*[MyField],0)/20=0,[MyField],IIf([MyField]-Round(20*[MyField],0)/20>0,Round(20*[MyField],0)/20+0.05,Round(20*[MyField],0)/20))

 

[pbaldy]

This may also be an alternative:

http://support.microsoft.com/kb/209996/en-us

 

[MarkK]

For rounding I suggest this formula...

Round(x / r) * r

...where x is the number you want rounded and r is the number to round to.
And to round to the nearest nickel you can use 0.05 rather than 20, as Mr Browne suggests...

Round(x / 0.05) * 0.05

(I suspect Mr. Browne makes an oversight here when he uses a different formula to round to numbers < 1 than he uses to round to numbers > 1.)

And to round up: Since Int() always rounds down, to round up you can negate x and then negate the whole expression...

-Int(-x / r) * r

...where x is the number to round, and r is the number to round to.
Cheers,
Mark

 

[John Big Booty]

For rounding I suggest this formula...

Round(x / r) * r

...where x is the number you want rounded and r is the number to round to.
And to round to the nearest nickel you can use 0.05 rather than 20, as Mr Browne suggests...

Round(x / 0.05) * 0.05

...

I read the OP to say he wanted to round up to the next 5 cents, rather than to the nearest 5 cents.

 

[razorking]

I do want to round up to the nearest nickel. So - I want 10.97 to round up to 11.00 (as an example).

I do understand that this:
RoundUp: Round([MyField]/0.05)*0.05
works for rounding to the nearest .05

I am still not seeing how to get it to round 10.97 up to 11.00. It wants to round it to 10.95

Mark comments on rounding up - but how would I write that expression in a query - where MyField is the name of the field I am trying to round up - to nearest .05 (nickel)?

 

[John Big Booty]

Have you check the code presented at Post #4?

 

[MarkK]

Read the whole post...

And to round up: Since Int() always rounds down, to round up you can negate x and then negate the whole expression...

-Int(-x / r) * r

...where x is the number to round, and r is the number to round to.
Cheers,
Mark

...so to explicitly plug in your values, try ...

RoundUp: -Int(-[YourField] / 0.05) * 0.05

...and see what you get.

 

[razorking]

John, Mark,

Actually it looks like both of your solutions will work.

I very much appreciate the assistance!

Thanks!