Hello all,

I am trying to total each line on a subform and end up with a grand total for a given record. However, the line totals will not add correctly which in turn gives me an incorrect grand total. Please review this info to see if a simple fix is evident.

The first 2 items perform as they should. All dollar amounts $XXX.0001 and over round to the next cent. This is what I want. The grand total code does not add the rounded values.


Private Sub UnitPrice_AfterUpdate()
    [LineSubTotal] = Me.Multiplier * Me.UnitPrice

End Sub
        [LineSubTotal] rounds correctly = Example 941 x $0.145 = $136.45
        [LineSubTotal] is set to 2 decimal places.


Private Sub SurchargePercent_AfterUpdate()
    [SurchargeTotal] = Me.LineSubTotal * Me.SurchargePercent / 100

End Sub
        [SurchargeTotal] rounds correctly = Example $136.45 x 4% = $5.46
        [SurchargeTotal] is set to 2 decimal places.



=Sum(Nz([LineSubTotal])+Nz([SurchargeTotal]))
    Does not add rounded currency correctly = Example $136.45 + $5.46 = $141.90

I have tried codes CLng, CCur, Rnd.:(

Thank you for your time!


D. Bartelt

__________________
D. Bartelt

Hello again,

Some more info on my problem. The problem lies within the table. I have formatted the [LineSubTotal], [SurchargeTotal] and [LineTotal] fields to be currency with 2 decimal places. The fields show 2 decimal places until I click on that field. At that point it shows 3 - 4 decimal places. The codes I have written will not take the visible value, they take the true value. How can I force 2 decimal places that will go to the next cent? If it is $00.0101, I need to force it to $00.02.

Thanks.

D. Bartelt

The currency data data tyoe always has 4 decianls place to handle rounding.

If you do not want tis, you must round all the data going into the table to 2 places or in your formulas, rond each field to 2 places before doing any calculation.



Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

Hi Boyd,

Thanks for your reply. I found what I was looking for at Microsoft.com, Access help. I wrote this module in my database and it performs just as I was hoping.

My calculations use the "RoundCC" function. Ie. =Sum(RoundCC(Nz([Multiplier])*Nz([UnitPrice])*Nz([SurchargePercent])/100))


'************************************************* ********************
' Declarations section of the module
'************************************************* ********************

Option Compare Database
Option Explicit
Const Factor = 100

'************************************************* ********************
'RoundAU and TruncAU are designed to be added to the
'AfterUpdate property on a form control.
'************************************************* ********************
Function RoundAU(X As Control)

    X = Int(X * Factor + 0.5) / Factor

End Function
Function TruncAU(X As Control)

    X = Int(X * Factor) / Factor

End Function

'************************************************* ********************
' RoundCC and TruncCC are designed to be used in
' expressions and calculated controls on forms and reports.
'************************************************* ********************

Function RoundCC(X)

    RoundCC = Int(X * Factor + 0.5) / Factor

End Function

-----------

Hope this helps others in need of correct currency rounding.

Have a good day!:)

D. Bartelt