Search code examples
sqlroundingms-access-2013

How to ROUNDUP a number in Access 2013?

For Access 2013, I need a way to round up any fractional numbers to the next whole number in an SQL query.

Example: 

SELECT ROUNDUP(NumberValues) FROM Table1

In the above query, a row with 1.25 should return as 2 after querying.

As far as I know, there's no ROUNDUP function in Access 2013 for use in a SQL query statement.

Solution

  • 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.