How to convert currency into double in VBA?

Question

I have three textboxes and I get their value like this:

Dim X, Y, W As Double
X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)
Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID)
W = DLookup("Monthly_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID)

But when I change the value of textbox like this

Form_frmPlatej.Deposit_before = X - W + Y

I get a Type mismatch error. All textboxes are currency. How do I calculate new record and put that number in the "Deposit_before" textbox?

Summ, Deposit_before, Monthly_payment are currency data type in my table. Deposit_before is mostly negative.

Here is my whole code for button click

Private Sub Command13_Click()

a1 = DLookup("Inhabitant", "tblClient", "ID = " & Form_frmMain!ID)
B1 = DLookup("PriceTBO", "tblPrice")
c1 = DLookup("Republican", "tblClient", "ID = " & Form_frmMain!ID)
d1 = DLookup("Regional", "tblClient", "ID = " & Form_frmMain!ID)
e1 = DLookup("Local", "tblClient", "ID = " & Form_frmMain!ID)

A = DLookup("IDP", "tblPlatej", "ID= " & Form_frmPlatej!ID)
B = DLookup("Type_of_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID)
C = DLookup("Year", "tblPlatej", "ID= " & Form_frmPlatej!ID)
D = DLookup("Month", "tblPlatej", "ID= " & Form_frmPlatej!ID)

Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID) // Problem here
W = DLookup("Monthly_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID) //Problem here
X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)

i = Form_frmPlatej.Month.ListIndex
j = Form_frmPlatej.Year.ListIndex
den = DLookup("Date", "tblPlatej", "IDP = " & Form_frmPlatej!IDP)

If X <> " " Then
With Me.Recordset
If Me.Recordset.BOF = False And Me.Recordset.EOF = False Then
.MoveFirst
End If
.AddNew
.Edit

Form_frmPlatej.Deposit_before = X - W + Y  //Problem here

Form_frmPlatej.IDP = A + 1
Form_frmPlatej.Type_of_payment = B
If i = 11 Then
Form_frmPlatej.Year = Year.ItemData(j + 1)
i = -1
Else
Form_frmPlatej.Year = Year.ItemData(j)
End If

Form_frmPlatej.Month = Month.ItemData(i + 1)
Form_frmPlatej.Date = DateAdd("m", 1, den)

If c1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (c1 * (a1 * B1)) / 100

ElseIf d1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (d1 * (a1 * B1)) / 100

ElseIf e1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (e1 * (a1 * B1)) / 100
Else
Form_frmPlatej.Monthly_payment = a1 * B1
End If
.Update

End With

Else
MsgBox ("Please enter number")
End If

End Sub

I am completely confused.

I am curious as to why you wish to convert to double from currency? Why not work with the currency type when that is what you have? See stackoverflow.com/questions/3730019/… — Fionnuala

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2012-07-11T06:57:48

I bet your problem is the following. When you say this:

Dim X, Y, W As Double

you think you've done this:

Dim X As Double, Y As Double, W As Double

but what you've really done is this:

Dim X
Dim Y
Dim W As Double

This is a classic VBA mistake. Most VBA programmers have made it, and that's why most VBA programmers fall back on declaring only one variable per Dim statement (i.e. one per line). Otherwise it's way too easy to make that mistake, and difficult to spot it afterwards.

So with Dim X and Dim Y you've implicitly declared X and Y as Variant type (equivalent to Dim X As Variant and Dim Y As Variant).

Why does this matter? When you then say this:

X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)
Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID)

maybe one of those two DLookup unexpectedly returns something that isn't a number, for example a string. Your variant X or Y will accept this without complaining; the Variant acquires the type of the thing on the right hand side of the assignment.

However, when you try to do math with these values, X - W + Y will throw a type mismatch error if X and/or Y is a string.

See also this earlier answer of mine, from which I reused some of the wording: https://stackoverflow.com/a/11089684/119775

How to convert currency into double in VBA?

1 Answers