How do you round a calculated field value?

[aquabp]

I have an invoice total column that is based on a calculated field. On the invoice I can format it to currency with 2 decimal places and all is good. When I receive payment I want to compare what came in with that calculated invoice field.

I have set the format to integer on the query but when I compare that value with the amount that was paid the comparison always uses the calculated number. The result kicks out on the payment issue report.

Any help or ideas would be greatly appreciated!

[JoeM]

Why are you using Integer? An integer is a whole number that does not allow for decimals.
So a field formatted as currency, which allows for decimals, will not match up very well with a field formatted as integer.

[ranman256]

cant you just use FORMAT, since it shows the real number (but only shows 2 digits)?
otherwise you can use,ROUND([field1]/[field2], 2) but that will produce rounding error.
tho,
whichever way your company wants to use.

I suggest, NOT calculating the final number but rather calculate it into a field, AND keep the original. My boss wanted both the long# and the calculated to show what data go lost and rounding error.

[aquabp]

Great! Thanks! Yes I have been trying to use format but the properties box on a query does not have a setting for a decimal which I found weird. I can dump it into a table and get the results but trying to avoid creating another table. Thanks again!