Convert String to Double

[hhzyousafi]

I have been trying for the better part of the past hour trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error. Can someone please help me?

 

[pr2-eugin]

Normally you would use some of the Casting functions, like CDbl, CLng to change it.

 

[hhzyousafi]

Hi Paul,

Here is the I am using:

Dim ActualManHourstblTasks As Double
Dim ActualManHourstblTaskHistory As Double
Dim ActualManHoursCalculatedString As Double
Dim ActualManHoursCalculatedDouble As Double

If Me.OpenArgs = "Edit" Then
ActualManHoursCalculatedString = "SELECT SUM(ActualManHours) FROM tblTaskHistory WHERE TaskID = '" & txtTaskID.Value & "';"
ActualManHoursCalculatedDouble = CDbl(ActualManHoursCalculatedString)

DoCmd.SetWarnings (False)
DoCmd.RunSQL ("UPDATE tblTasks SET tblTasks.ActualManHours = '" & ActualManHoursCalculated & "'WHERE tblTasks.TaskID = '" & txtTaskID.Value & "';")
DoCmd.SetWarnings (True)
Else
ActualManHourstblTasks = Nz(DLookup("ActualManHours", "tblTasks", "TaskID = '" & txtTaskID.Value & "'"))
ActualManHourstblTaskHistory = Nz(txtActualManHours.Value)
ActualManHoursCalculated = ActualManHourstblTasks + ActualManHourstblTaskHistory
If txtActualManHours.Visible = True Then
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("UPDATE tblTasks SET tblTasks.ActualManHours = '" & ActualManHoursCalculated & "'WHERE tblTasks.TaskID = '" & txtTaskID.Value & "';")
DoCmd.SetWarnings (True)
Else
End If
End If​

When I comment this block of code the page works as intended.

 

[pr2-eugin]

Because, you cannot use a SELECT Query like that. You need a Recordset object. Or simply use a DSum !

Dim ActualManHourstblTasks As Double
Dim ActualManHourstblTaskHistory As Double
Dim ActualManHoursCalculatedString As Double
Dim ActualManHoursCalculatedDouble As Double

If Me.OpenArgs = "Edit" Then
    ActualManHoursCalculatedDouble = [COLOR=Red]Nz(DSum("ActualManHours", "tblTaskHistory", "TaskID = '" & Me.txtTaskID & "'"), 0)[/COLOR]
    
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL ("UPDATE tblTasks SET tblTasks.ActualManHours = '" & ActualManHoursCalculated & "'WHERE tblTasks.TaskID = '" & Me.txtTaskID & "';")
    DoCmd.SetWarnings (True)
Else
    ActualManHourstblTasks = Nz(DLookup("ActualManHours", "tblTasks", "TaskID = '" & Me.txtTaskID & "'"), 0)
    ActualManHourstblTaskHistory = Nz(txtActualManHours)
    ActualManHoursCalculated = ActualManHourstblTasks + ActualManHourstblTaskHistory
    If txtActualManHours.Visible = True Then
        DoCmd.SetWarnings (False)
        DoCmd.RunSQL ("UPDATE tblTasks SET tblTasks.ActualManHours = '" & ActualManHoursCalculated & "' WHERE tblTasks.TaskID = '" & Me.txtTaskID & "';")
        DoCmd.SetWarnings (True)
    End If
End If

 

[Mile-O]

Dim ActualManHoursCalculatedString As Double
....
ActualManHoursCalculatedDouble = CDbl(ActualManHoursCalculatedString)

Also, why would you use CDBl() on something that is already defined as a Double?

 

[pr2-eugin]

Dim ActualManHoursCalculatedString As Double
....
ActualManHoursCalculatedDouble = CDbl(ActualManHoursCalculatedString)

Also, why would you use CDBl() on something that is already defined as a Double?

I think that is because the OP thought using a SELECT Statement like this

"SELECT SUM(ActualManHours) FROM tblTaskHistory WHERE TaskID = '" & txtTaskID.Value & "';"

would return the Sum value, which he/she thought was a Double value disguised as a String, when the variable is nothing but a String !

 

[hhzyousafi]

Thank you Paul and Mile-O. I guess sometimes you just have to take a step back and see everything because this was fairly basic and I just completely missed it.

 

[pr2-eugin]

Thank you Paul and Mile-O. I guess sometimes you just have to take a step back and see everything because this was fairly basic and I just completely missed it.

It's alright ! We all were there once. The point to take is, you have learnt something new today ! Good Luck !