Roundup 1

[b3026]

Is it possible to roundup a number calculated via a formula? Example: My formula currently reads:

=Round([Price]/.6)

Can the answer be rounded up to the nearest 10? In other words, we need an answer of $14.20 to round up to $20.00. $208.00 needs to round up to $210.00.

All help will be appreciated.

 

[PaulBricker]

Got done a little sooner than I thought. Using the expression in a calculated control or as the Field expression in a Query didn't work as you are well aware. Sending a value to the Function worked great. As a one liner, it's a DUD!!! Floating point rounding error is my guess but even setting the Format property of the textbox to Currency didn't help.


Paul

 

[GingerR]

very interesting tho. I can't I can no longer assume both ways are safe. Something to look out for I guess...

 

[PaulBricker]

I was very surprised at the problem. If I have some time today, I'm going to try and disect it and see where it comes apart.

Paul

 

[PaulBricker]

This version seems to have solved the problem.

Int((((nVal + 0.999) / 100) + 0.09) * 10) * 10

Apparently, and I can't explain why, the part that was messing things up was

-1 *  Int(-1 * (nVal))

This expression rounds a number up to the next 1 digit. So
1.2 rounds to 2
2.1 rounds to 3
9.9 rounds to 10
etc.
When the expression was used in the form, the part of the expression messing things up actually worked as expected and the results, although not what we wanted, were correct. When the Function runs, the offending part of the expression is ignored and the results, although what we wanted to see, were incorrect. Don't ask me why but that's the clearest explanation of what happens. In the process, I think I ended up with an expression that actually works in a form and function.

Paul

 

[MichaelRed]

hmmmmmmmmmmmmmmmm ...

anyone (else) notice that B3026 has drpooed out of the bidding? Is it POSSIBLE that this (continuing) activity is just to impress 'ourselves'?

As far as I know, the function I posted works pretty well (in use for a couple of years in a multiuser system). Although it is somewhat more elaborate than the requirement, it also provides (via that elaboration) some additional flexability.

MichaelRed

 

[PaulBricker]

Undoubtedly, but when an expression works inside a function but doesn't as a stand alone, it seems professional, in my opinion, to investigate why. I was aware that B3026 already was using a solution before I ever posted mine but didn't think that was necessarily a reason not to.

Paul

 

[GingerR]

Michael I wasn't trying to impress anyone. I guessed B3026 already figured it out, but it was interesting to me to fiddle around with Paul's solution since it presented some oddity. There's not a "sidebar" forum here for us. I suppose we could use email but the TT suggest we don't give out personal info. Plus this banter could help others. I know I've browsed around through other people's ramblings and learned a lot...