This is an old Access trick I learned a very long time ago, and it makes use of the way Access handles fractional, negative numbers. Try this:
-Int(-[DecimalValue])
It's odd, but it will always round your numbers up to the nearest whole number.
Answer from RLH on Stack OverflowroundUp Function | Access World Forums
excel - Rounding in MS Access - Stack Overflow
sql - How to ROUNDUP a number in Access 2013? - Stack Overflow
Round UP to next integer in MS Access 2000 in a query?
This is an old Access trick I learned a very long time ago, and it makes use of the way Access handles fractional, negative numbers. Try this:
-Int(-[DecimalValue])
It's odd, but it will always round your numbers up to the nearest whole number.
Test this: Round(yournumber + 0.5, 0)
Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
Application.Round(58.55, 1)
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
To expand a little on the accepted answer:
"The Round function performs round to even, which is different from round to larger."
--Microsoft
Format always rounds up.
Debug.Print Round(19.955, 2)
'Answer: 19.95
Debug.Print Format(19.955, "#.00")
'Answer: 19.96
ACC2000: Rounding Errors When You Use Floating-Point Numbers: http://support.microsoft.com/kb/210423
ACC2000: How to Round a Number Up or Down by a Desired Increment: http://support.microsoft.com/kb/209996
Round Function: http://msdn2.microsoft.com/en-us/library/se6f2zfx.aspx
How To Implement Custom Rounding Procedures: http://support.microsoft.com/kb/196652
I found a ROUNDUP equivalent from this link: http://allenbrowne.com/round.html#RoundUp
To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this: - Int( - [MyField])
As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.
To round up to the higher cent, multiply by -100, round, and divide by -100: Int(-100 * [MyField]) / -100
The syntax is counter-intuitive, but it works exactly as I intended.
I have found the easiest way to round up a number in access is to use the round function like this:
Round([MyField]+0.4,0)
The number 10.1, for example then becomes 10.5. When the round function is applied, it rounds up to 11. If the number is 10.9, adding 0.4 becomes 11.3, which rounds to 11.