1. Erratic Rounding (Access XP)

I am trying to use a simple formula to round up the results from a query to the nearest whole number. The formula works perfectly 90% of the time.
I have 3 variables: qty, NumberPerPallet, SpacesPerPallet. The formula needs to take the quantity/NumberPerPallet*SpacesPerPallet and then round up. If I take Int(2/3*2) =2, everything is fine. But Int(1/3*2)=1 when it should be 2. I've tried using Int, Fix, IIF to no avail.

Tried:
IIF(int(x)=x,x,x+1)
-(int(-number)
IIF(number>int(number),int(number)+1,number
Am I just brain dead? Any ideas would be greatly appreciated.

2. Re: Erratic Rounding (Access XP)

>>But Int(1/3*2)=1 when it should be 2<<

Really? That's not how I learned it in school.

3. Re: Erratic Rounding (Access XP)

The Int function returns the integer portion of a number.
(1/3)*2 = 1.5. the integer portion of that is 1

You say that youwant to "round up the results from a query to the nearest whole number".
Do you want to go to the nearest whole number or round up?

The CInt function will round your values to the nearest integer, but it will round 1.5 down to 1.

4. Re: Erratic Rounding (Access XP)

>>(1/3)*2 = 1.5. <<

What type of calculator are you using to get that result?

5. Re: Erratic Rounding (Access XP)

Try

INT((x/y*z)+0.99)

You can extend the number of nines if you are likey to need to round up numbers below 0.01. (i.e if y*z could be greater than 100)

6. Re: Erratic Rounding (Access XP)

Who knows what I was thinking when I wrote >>(1/3)*2 = 1.5. <<,

but it remains true that the Int function always rounds down. (for positive numbers at least, I forget what happens for negatives).

7. Re: Erratic Rounding (Access XP)

You wrote

>> the Int function always rounds down

This is what Int is intended to do. It doesn't round, but remove the fractional part of a number. From VB6 help:
<hr>Both Int and Fix remove the fractional part of number and return the resulting integer value.

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.<hr>
To round numbers to the nearest integer, use (surprise, surprise) the Round function, or use Int(x+0.5).

8. Re: Erratic Rounding (Access XP)

Int( ) rounds down, so Int(-1.5) becomes -2. Fix( ) just takes the integer part of the argument, so Fix(-1.5) = -1.

9. Re: Erratic Rounding (Access XP)

You wrote:
>>...or use Int(x+0.5). <<

This won't consistently work. Try various values for x, such as 1.0, 1.5, 2.0, 2.5.

You should use Cint(x) if you want to convert to an integer. This actually follows rounding rules, Int merely takes the integer part of the arguement.

10. Re: Erratic Rounding (Access XP)

Hi Mark,

It depends on how you define "consistently". When rounding a number ending in .5, what is the nearest integer? After all, 1.5 is equidistant from 1 and 2.

CInt uses what is called "banker's rounding" - numbers ending in .5 are rounded to the nearest even integer. So 1.5 and 2.5 are both rounded to 2, 3.5 and 4.5 are both rounded to 4, etc. If that's what you want - fine.

Int(x+0.5) always rounds numbers ending in .5 up to the next highest integer. This is what mathematicians mostly use. The ROUND function in Excel acts like this.

So, you can't say that one method is correct and the other one is wrong. You must decide what you expect rounding to do and then pick a method that does that.

Regards,
Hans

11. Re: Erratic Rounding (Access XP)

Using Int to "round" as you suggested only works for rounding to whole numbers. All other rounding in Access uses the "round to the even digit" approach when the rounded digit is 5. This applies to using the Round function (Access2000 and above) and Cint, and indeed any math that you do that eventually converts to an integer or currency, or that uses the format function. So I guess its a matter of consistency.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•