How do I round in Access?

[PKM]

I have a report which is doing DLookUps to 'Total Charges' for printing orders, postage, copier, etc. The totals are currency with 2 decimals.
I then have an IIF statement totaling the fields from the report:
=IIf([txtAccountBalance] Is Null,0,[txtAccountBalance])+IIf([txtCopier] Is Null,0,[txtCopier])+IIf([txtPrinting] Is Null,0,[txtPrinting])+IIf([txtPostage] Is Null,0,[txtPostage])+IIf([txtUnPrintedTotal] Is Null,0,[txtUnPrintedTotal])+IIf([txtServFee] Is Null,0,[txtServFee])

This total now contains a number with 3 decimal places.
So when I calculated the new balance:
=IIf([txtTotalBalanceDue] Is Null,0,[txtTotalBalanceDue])-IIf([txtPayments] Is Null,0,[txtPayments])

My calculation does not round accordingly. I am 1 penny short.

Any help is appreciated.

Thank You

 

[PHV]

The totals are currency with 2 decimals
This total now contains a number with 3 decimal places
How did you manage to get 3 decimal places when adding currency with two decimals ?

Anyway I'd use the following formulas:
=Nz([txtAccountBalance],0)+Nz([txtCopier],0)+Nz([txtPrinting],0)+Nz([txtPostage],0)+Nz([txtUnPrintedTotal],0)+Nz([txtServFee],0)

=Nz([txtTotalBalanceDue],0)-Nz([txtPayments],0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

How did you manage to get 3 decimal places when adding currency with two decimals ?

Oooooh, I know, I know! <Waving hand frantically in the air>

Someone is using Single or Double data types!

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PKM]

Thanks to both of you, but neither resolved the rounding issue.
I used the Nz function did not change my final balance.
I verified that the fields from the tables that were being added were 2 decimal places, all were currency.
Changed the fields on the form to be 2 decimal places where some were auto.
I am still off by a penny on one of the statements.]

Thanks

 

[Remou]

Even though currency shows two decimal places, it stores a lot more than that. I find it is best to use format before writing to the table if you are doing any calculations to get the values. Format rounds up at 0.045*, unlike Round, which rounds down (this is statistically more accurate, but most business prefer to round up).

*
Format(0.045,"#,#0.00")

 

[JoeAtWork]

Formatting affects how the number is displayed, not what it stores.

Therefore, you might have two numbers:

$1.243333
$1.242222

You will see:
$1.24
$1.24

And you will wonder why it adds up to $2.49 instead of $2.48. The answer is it actually adds to $2.485555, which rounds to $2.49.

Which is why you have to round each number before adding them all together.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Remou]

Joe, I fear you miss my point. I went through this in some detail, due to VAT.

Format(MyField, ...) is a display.

MyField=Format([Amount]*[VAT} "#0.00")
stores the number rounded up in every case, whereas round stores the number rounded down.

I find companies prefer to charge the customer 0.005, rather than losing 0.005.

 

[JoeAtWork]

Remou - I did not mean my post as a comment on yours. It was meant to give more background info to the OP.

Joe Schwarz
Custom Software Developer

http://www.plandev.net