How to stop currency from rounding up

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
mrBrightsideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrBrightsideAuthor Commented:
thanks mate, thats worked, what does cdbl mean then? currency double?
0
Arthur_WoodCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.