bikeski
asked on
Type Mismatch on Variant
Hello,
I am getting a "Type Mismatch" error on the line:
Margin = (Total - TotalCost) / Total
All three variables are defined as Variant.
Thanks,
Ron
I am getting a "Type Mismatch" error on the line:
Margin = (Total - TotalCost) / Total
All three variables are defined as Variant.
Thanks,
Ron
Public Sub StoreMargin(BegDate1, EndDate1, Cell1)
Dim conSQL, conSQLCost As ADODB.Connection
Dim strSQL, strSQLCost As String
Dim i As Integer
Dim Total, TotalCost, Margin As Variant
Dim rs, rsCost As ADODB.Recordset
Total = 0
i = 0
Set conSQL = New ADODB.Connection
conSQL.Open "Server=THEDUB;DRIVER=SQL Server;Database=***;Password=***;User ID=***"
strSQL = "Select Sum(PUBLIC_TransactionEntry.Quantity * PUBLIC_TransactionEntry.price) AS sum_sales" & _
" From PUBLIC_Transaction Inner Join" & _
" PUBLIC_TransactionEntry On PUBLIC_Transaction.TransactionNumber =" & _
" PUBLIC_TransactionEntry.TransactionNumber Inner Join" & _
" PUBLIC_Item On PUBLIC_TransactionEntry.ItemID = PUBLIC_Item.ID Inner Join" & _
" PUBLIC_Department On PUBLIC_Item.DepartmentID = PUBLIC_Department.ID" & _
" WHERE PUBLIC_Department.Name NOT LIKE '%Gift%Card%' " & _
" AND PUBLIC_Transaction.Time >= (" & BegDate1 & ")" & _
" AND PUBLIC_Transaction.Time < (" & EndDate1 & ")" & _
" AND PUBLIC_Department.Code NOT Like 'E/%'" & _
" AND PUBLIC_Department.code NOT Like 'T/%'"
Set rs = New ADODB.Recordset
rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
Total = rs.GetRows(-1, 1, "sum_sales")
Else
Total = 0
End If
Set conSQLCost = New ADODB.Connection
conSQLCost.Open "Server=***;DRIVER=SQL Server;Database=***;Password=***;User ID=***"
strSQLCost = "Select Sum(PUBLIC_TransactionEntry.Quantity * PUBLIC_TransactionEntry.cost) AS sum_cost" & _
" From PUBLIC_Transaction Inner Join" & _
" PUBLIC_TransactionEntry On PUBLIC_Transaction.TransactionNumber =" & _
" PUBLIC_TransactionEntry.TransactionNumber Inner Join" & _
" PUBLIC_Item On PUBLIC_TransactionEntry.ItemID = PUBLIC_Item.ID Inner Join" & _
" PUBLIC_Department On PUBLIC_Item.DepartmentID = PUBLIC_Department.ID" & _
" WHERE PUBLIC_Department.Name NOT LIKE '%Gift%Card%' " & _
" AND PUBLIC_Transaction.Time >= (" & BegDate1 & ")" & _
" AND PUBLIC_Transaction.Time < (" & EndDate1 & ")" & _
" AND PUBLIC_Department.Code NOT Like 'E/%'" & _
" AND PUBLIC_Department.code NOT Like 'T/%'"
Set rsCost = New ADODB.Recordset
rsCost.Open strSQLCost, conSQLCost, adOpenStatic, adLockOptimistic
If Not rsCost.EOF Then
TotalCost = rsCost.GetRows(-1, 1, "sum_cost")
Else
TotalCost = 0
End If
' If Total <> 0 Then
Margin = (Total - TotalCost) / Total
Range(Cell1).Value = Margin
' Else
' Range(Cell1).Value = 0
' End If
conSQL.Close
conSQLCost.Close
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And this:
Total = rs.GetRows(-1, 1, "sum_sales")
to:
Total = rs.GetRows(-1, 1, "sum_sales")(0,0)
Kevin
Total = rs.GetRows(-1, 1, "sum_sales")
to:
Total = rs.GetRows(-1, 1, "sum_sales")(0,0)
Kevin
ASKER
Thanks, adding (0,0) did the trick.
FYI, I tried changing to the Double variable type, that didn't work.
FYI, I tried changing to the Double variable type, that didn't work.
Wayne