round down in a query

[jschneider998]

How can I use the Rounddown function in a query? I am trying to average 2 numeric fields in a third field but I am getting answers with decimals. I would like to have this third field rounded down to the next whole number. Is that possible?

 

[mrssevans]

In your query, if you right click and go to properties you can set the third fields decial places. Hope this helps, Sam

 

[dcx693]

Setting the decimal places in the query field properties only formats the number differently, it doesn't actually change it numerically.

If you put an expression like this in the Field: line of your query should work:
Rounddown(([Field1]+[Field2])/2,0)
though for some reason the rounddown function is not working on my Access 2000 machine right now.

 

[dcx693]

I'm still having trouble getting the rounddown function to work. If you are also, try subtracting .5 from your average before applying the round([number],0) function to it. That should achieve the same effect.

 

[dcx693]

You can also try using the Int() function. It doesn't quite operate the way I expected it to, but in your case it works. For example, Int(-2.1) is 3. I always thought of the Int function as returning the integer portion of a number. In the case of -2.1, I'd expect -2, but the Int(-2.1) is actually 3. It rounds numbers down. If you just want the integer portion of a number, Access provides the Fix() function.

 

[jon98548]

How about if I want to round up to the next whole number I put: Int([MyField]+1)

This will return 2 if MyField is 1.2 or 1.7. Any disadvantage to this?

 

[Mile-O]

Just as an aside - Int returns the exact same data type put into it.

So, if you put a single into it:

i.e Int(singlevalue)

then, what is returned is still a single but without the decimal places.

CInt() actually converts to an integer.

 

[jon98548]

That's good to know. Thanks, Mile.

 

[jon98548]

No, that doesn't work. Where Int drops the fractional part, CInt returns the low whole number below 0.5 and the high whole number above 0.5. So, CInt(1.3) = 1 and CInt(1.6) = 2. Seems kind of screwy there isn't a simple term to round up.

 

[jon98548]

Of course, if I was thinking at all. I would realize CInt(1.3+0.5)=2 and CInt(1.6+0.5)=2. OK, I think I'm all better now.

 

[Mile-O]

One thing to note about the CInt() function is that if a value is exactly 0.5 it rounds a value to the nearest even number.

So 2.5 rounds to 2 whereas 3.5 would round to 4.

 

[jon98548]

So the most reliable way for me to round up always is to call up a function?

 

[namliam]

Or if you want the nearest integer rounded up why not:

Int (value+1)

Regards

 

[jon98548]

Yes, I think you're right. I was concerned with the data type with what Mile-O posted, but I set my data type as an integer. I think nthat will do it. Haven't found any errors in testing.