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?

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

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.

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.

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.

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?

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.

That's good to know. Thanks, Mile.

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.

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.

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.

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

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

Int (value+1)

Regards

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.