Results 1 to 11 of 11
Thread: Erratic Rounding (Access XP)

20020801, 17:48 #1
 Join Date
 Aug 2002
 Location
 Portland, OR, USA
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20020801, 19:17 #2
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,464
 Thanks
 1
 Thanked 39 Times in 38 Posts
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.Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020801, 22:22 #3
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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.Regards
John

20020802, 01:27 #4
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,464
 Thanks
 1
 Thanked 39 Times in 38 Posts
Re: Erratic Rounding (Access XP)
>>(1/3)*2 = 1.5. <<
What type of calculator are you using to get that result?Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020802, 03:05 #5
 Join Date
 Jan 2001
 Location
 Brisbane, Queensland, Australia
 Posts
 352
 Thanks
 0
 Thanked 1 Time in 1 Post
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)David Grugeon
Brisbane Australia

20020802, 04:47 #6
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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).Regards
John

20020802, 05:14 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.To round numbers to the nearest integer, use (surprise, surprise) the Round function, or use Int(x+0.5).
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>

20020802, 12:16 #8
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,464
 Thanks
 1
 Thanked 39 Times in 38 Posts
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.
Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020802, 12:24 #9
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,464
 Thanks
 1
 Thanked 39 Times in 38 Posts
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.Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20020802, 12:56 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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

20020802, 14:29 #11
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,464
 Thanks
 1
 Thanked 39 Times in 38 Posts
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.
Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.