• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

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
0
mrBrightside
Asked:
mrBrightside
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now