Rounding Up to Nearest Multiple of 10

[meangene]

What Access function would I use to round an integer up to the nearest
multiple of 10 (e.g., 34 to 40, 87 to 90, etc.). In Excel one can use the
Ceiling function. Thanks in advance!

 

[[MVP] S.Clark]

There isn't one. You'll have to roll your own or tap into the Excel library
and use that one.

BTW, this forum is really for Access SQL Queries.

 

[Ken Snell [MVP]]

This expression should get you pretty close:

-Int(-MyNumber/10) * 10

 

[Steve Schapel]

Meangene,

I am not sure whether there is a more elegant way, but this works...

([YourNumber]\10-([YourNumber] MOD 10 <>0))*10

 

[Tom Lake]

(e-mail address removed)...

What Access function would I use to round an integer up to the nearest
multiple of 10 (e.g., 34 to 40, 87 to 90, etc.). In Excel one can use the
Ceiling function. Thanks in advance!

40 isn't the nearest multiple of 10 to 34, 30 is. If you want to always
round up, then use this:

Function Round10(n)
Round10 = 10 * Int(n / 10 + 1)
End Function

Tom Lake

 

[Steve Schapel]

Meangene,

Or, a variation (improvement?) on the theme...
(([YourNumber]-1)\10+1)*10

 

[meangene]

With the exception of one, thanks for everyones helpful and polite answers!
Two of the suggested solutions work well.

 

[Ken Snell [MVP]]

Or, to work with both positive and negative numbers:

-Sgn(MyNumber)*Int(-Sgn(MyNumber)*MyNumber/10) * 10
--

Ken Snell
<MS ACCESS MVP>

 

[Steve Schapel]

Ken,

I would regard "rounding up" to be:
34 rounds up to 40
-34 rounds up to -30

No?

 

[Ken Snell [MVP]]

I have found that everyone's "definition" of rounding up varies, often
depending upon the context.. so I just assume the "up" means in the
direction of the larger magnitude of the number, not the value of the number
< g >.

 

[Steve Schapel]

Fair enough. Anyway, just for fun, this works for *my* definition of
"up"...

((MyNumber+(MyNumber>0))\10-(MyNumber>0))*10

Maybe it's the Southern Hemisphere <g>

 

[Ken Snell [MVP]]

Fair enough. Anyway, just for fun, this works for *my* definition of
"up"...

((MyNumber+(MyNumber>0))\10-(MyNumber>0))*10

Maybe it's the Southern Hemisphere <g>

Best explanation for this that I've ever heard!

< BG >