We help IT Professionals succeed at work.

# How to stop currency from rounding up

on
Hi,

I have a form to calculate order totals, i have a subtotal, vat and order total field. the code is as follows to calculate the order:

Dim OrderTotal As Integer
Dim VAT As Double
Dim CalculateProducts As Boolean
Dim CalculateLabour As Boolean

VAT = Me.OrderVAT.Value

If IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = True And IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = True Then
MsgBox "Nothing to calc", vbCritical
ElseIf IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = False And IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = True Then
OrderSubTotal = Me!subfrmOrderJob!txtlabourTotal.Value
ElseIf IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = True And IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = False Then
OrderSubTotal = Me!subfrmProductOrder!txtProductTotal.Value
Else
OrderSubTotal = Me!subfrmProductOrder!txtProductTotal.Value + Me!subfrmOrderJob!txtlabourTotal.Value
End If

OrderTotal = ([OrderSubTotal] *VAT) + OrderSubTotal

Me.OrderSubTotal.Value = OrderSubTotal
Me.OrderTotal.Value = OrderTotal

problem is, if i perform this calculation, it populates the ordr total field with a value rounded up, so for instance if subtotal is 35 then it is calculating VAT to be 6 instead of 6.125....is there any way to calculate with decimals? i have decimals set to auto, the VAT field is double formatted to percent
Comment
Watch Question

## View Solution Only

Commented:
Does this change make a difference?

Also, where is OrderSubTotal declared - what TYPE is this variable?  It should be a DOUBLE as well

AW
``````Dim OrderTotal As Double
Dim VAT As Double
Dim CalculateProducts As Boolean
Dim CalculateLabour As Boolean

VAT = cDbl(Me.OrderVAT.Value)

If IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = True And IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = True Then
MsgBox "Nothing to calc", vbCritical
ElseIf IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = False And IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = True Then
OrderSubTotal = Me!subfrmOrderJob!txtlabourTotal.Value
ElseIf IsNull(Me!subfrmOrderJob!txtlabourTotal.Value) = True And IsNull(Me!subfrmProductOrder!txtProductTotal.Value) = False Then
OrderSubTotal = Me!subfrmProductOrder!txtProductTotal.Value
Else
OrderSubTotal = Me!subfrmProductOrder!txtProductTotal.Value + Me!subfrmOrderJob!txtlabourTotal.Value
End If

OrderTotal = ([OrderSubTotal] *VAT) + OrderSubTotal

Me.OrderSubTotal.Value = OrderSubTotal
Me.OrderTotal.Value = OrderTotal
``````

Commented:
thanks mate, thats worked, what does cdbl mean then? currency double?

Commented:
cDbl is a built-in function that converts the String value to a Double. c(conver)Dbl(Double).  If you are doing purely Currency calculations, then you could also declare values as Currency and use cCur (Convert to Currency).

However, I strongly suspect that the real problem was the declaration of

OrderTotal as Integer, that was changed to as Double (and could just as easily have been declared as Currency)

Whenever you do a calculation, make sure that all the variables involved in the calculation are declared as the same type.  VBA has a nasty habit of determining the 'lowest' type of the values on the right side of the = sign, converting all of the values to that 'lowest' type, and only after the calculation is complete, then converting the resultant value to the type on the left of the = sign.

As you originally had it, you had a type INTEGER on the right, so all of the type doubl;es were being converted to Integer (hence dropping the fractional part of the Double value).

Currency type is a special VBA data type that preserves 4 digits of accuracy to the right of the decimal point, and uses "Banker's Rounding" - you should look up "Banker's Rounding" in wikipedia, to be used in all calculations involviong Money.

AW