To round or not to round (calculated fields)

[malcolmedward]

Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.

Then the result is used to calculate how much loose coin will remain.

But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.

Thanks

 

[pr2-eugin]

What do you mean by Calculated Fields??

Is there anyway to turn off the rounding function in calculated fields

If you are using Calculated fields in your Table level, you might want to rethink your design.. Storing calculated value might be a wrong idea..

If they are in a Query or report, you can try Casting the Type of the Result to an Integer..

? [URL="http://www.techonthenet.com/access/functions/datatype/cint.php"]CInt[/URL](5.789)
 6 
? [URL="http://www.techonthenet.com/access/functions/numeric/int.php"]In[/URL]t(5.789)
 5

 

[malcolmedward]

First time using calculated fields in a table because first time using Access 2010 but if there are still problems then I will revert to using a query.

If I use the suggested format casting the result to an integer, then is the result simply displaying the whole number 5 but will still use 5.789 in further calculations or will it calculate using 5.0?

 

[pr2-eugin]

If the value is being stored by casting then it will use only 5, so if you use in the query, and I say again, use it in a query you can use the 5.789 for doing other calculations and 5 for displaying it..

 

[Pat Hartman]

VBA uses two divide operands. One rounds, one truncates. Lookup \ and / for specifics. You can also use MOD which returns just the remainder.

print 5 mod 2
 1 
print 5/2
 2.5 
print 5\2
 2