Rounding up to the next whole cent

[dbartelt]

Hello all,

This is my first thread within this forum.

My company is a service company, (Commercial Heat Treating), for heat treating metal automotive parts. We do not manufacture any products. We already use 2 separate databases for job control. Ie. Entering a new job. Generating process paperwork with processing instructions. Finally, when the order is completed and ready to be returned to our customer, we generate a shipping ticket for that order.

I am creating an Accounts Receivable database that will retrieve customer, part number, PO number, quantity and other selected information from the other 2 databases. I have linked the shipping ticket tables from each of them to the Accounts Receivable database. The invoicing form has a subform that is used to pull the outside data. I use a combo box to select a shipping ticket number. After the shipping ticket number is entered, the other information is populated to the proper fields. I have created 6 additional fields on the subform. Fields 1, 2 & 4 are for data entering. Fields 3, 5 & 6 will be populated with the results. The fields are in order:

(Units) is the multiplier field,
(UnitPrice),
(LineSubTotal) = (Units) * (UnitPrice)
(SurchargePercent) = 2% – 10%
(SurChargeTotal) = Me.LineSubTotal * Me.SurchargePercent / 100
(LineTotal) =nz([LineSubTotal])+nz([SurchargeTotal])

I am trying to total each line on the 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. The (LineTotal) values are $00.01 low, match exactly or $00.01 higher. 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 whole 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 (exact = 136.445)
[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 (exact = 5.458)
[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

The problem is any cent amount that is below .0049 rounds down. I want to force it to round up to the next whole cent.

I have tried codes CLng, CCur, Rnd.

Some more info on my problem. I believe 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 whole cent? If it is $00.0101, I need to force it to $00.02.

Thanks.

D. Bartelt

 

[Treason]

Ok, so I think you gave a little too much information in this post... but i will tackle your last question

How can I force 2 decimal places that will go to the next whole cent? If it is $00.0101, I need to force it to $00.02.

I tested this out and seems to work. Should always round UP to the highest cent

Private Sub Command2_Click()
    Dim Mystr As String
    
    Mystr = Mid(Me.Cost, InStr(Cost, ".") + 3)
    
    If Len(Mystr) > 0 Then
        If CInt(Mystr) > 0 Then
            Mystr = CInt(Mid(Me.Cost, InStr(Cost, ".") + 1, 2)) + 1
            Cost = Format(Left(Cost, InStr(Cost, ".")) & Format(Mystr, "00"), "Currency")
        End If
    End If
    
End Sub

Substitute Cost for whatever your field is

Cheers