Built-in function to round up to the next integer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Alternatively, if you're stuck on the round function, or don't want to create a custom function, you can still use round, but you will need to add 0.5 (exactly) to the number your rounding:

e.g., Round(1.0000001 + 0.5, 0) will equal 2

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Kevin,

> e.g., Round(1.0000001 + 0.5, 0) will equal 2

That won't work either. Access uses bankers rounding so if the base value is 2, the net value, 2.5, will still round to just 2.

Regards,

Patrick

ASKER

Thanks, this first one will work just fine.

Appreciate your quick help!

MashaCPA,

> Thanks, this first one will work just fine.

So you will never, ever pass the function a value that is itself an integer? Because if you do, you will not
get the answer you asked for...

ASKER

The values that are passed are defined as single in the table design.

ASKER

Actually, I see what you mean, now that I read it without rushing. Then LPurvis' revision should work better:

> Public Function RoundUp (dblNumber as double) as integer
RoundUp = int(dblNumber)
If RoundUp < dblNumber Then
RoundUp = RoundUp+1
End If
End Function

ASKER

Patrick, Leigh, thank you guys! I should have paid better attention!

Masha

I feel guilty....

Leigh Purvis

Don't - honestly.
:-)

(Kind of you to say so though)

Kevin,

There is no need for you to feel guilty at all. You made a good faith effort to answer the question. Being
incorrect is not a sin--it is a learning experience. I cannot count the number of times I made a suggestion
here that ultimately turned out to be the wrong answer. The key is to learn why the answer did not work,
and thus to grow better at the craft.

:)

Patrick

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Leigh Purvis

Well - to claim that it was my function wouldn't be strictly true - I merely slightly adapted the suggested solution.

If I could have been bothered to look for my usual rounding function... lol

Leigh,

Fair enough :)

Patrick

ASKER

Patrick,

In my project, the passed values cannot possibly be negative, but theoretically you are right.

ASKER

I accepted Leigh's answer because this is what I actually used. My passed values are between 1 and 48.

Thanks for all your efforts!