Rounding in a query (Access 97 SR2)

Topic

New Reply

I have a query which has a calculation in it. I’d like to round it up to the next number. So far I have found INT that knocks off the decimals and CINT that rounds to the nearest number i.e. o.4 rounds to 0 which is not what I want. I was thinking of adding 1 to the result using the INT command but that means all of the original round numbers will be rounded to the one about i.e. 1 will become 2 (think I explained that ok!).

Does anyone have any ideas?

Thanks in advance for any help you might provide.

Reply | Quote

Replies

Try this:

iif( yourNumber>int(yourNumber), int(yourNumber) + 1, yourNumber)

Reply | Quote

how about

iif(int(x)=x,x,x+1)

In theory (assuming infinite precision) that should work. However, be careful that you don’t run into problems with precision – you may need to use currency type to ensure that values that you think have no fractional part really don’t

Reply | Quote

Thanks Mark, that worked perfectly.
I haven’t tried your version yet Douglas, but i’ll give it a go when I get a minute.

Reply | Quote

If your number is always greater than or equal to zero you could use:

RoundedUpNumber = -int(-YourNumber)

It works for negative numbers, too, if you want the rounded number closer to zero (e.g., -1.1 rounds to -1 but not to -2). Note that for negative numbers, adding 1 as suggested by Mark and Douglas also gives you the rounded number closer to zero.

Tom

Reply | Quote