Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Type Mismatch on Variant

Posted on 2011-02-26
4
Medium Priority
?
783 Views
Last Modified: 2012-05-11
Hello,
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

Open in new window

0
Comment
Question by:bikeski
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 34989657
Try declaring them as Double instead of Variant. This should help you determine if one of those variables is not getting set to the correct data type.

Wayne
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 34989667
Change this:

TotalCost = rsCost.GetRows(-1, 1, "sum_cost")

to:

TotalCost = rsCost.GetRows(-1, 1, "sum_cost")(0,0)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34989670
And this:

Total = rs.GetRows(-1, 1, "sum_sales")

to:

Total = rs.GetRows(-1, 1, "sum_sales")(0,0)

Kevin
0
 

Author Closing Comment

by:bikeski
ID: 34989751
Thanks, adding (0,0) did the trick.

FYI, I tried changing to the Double variable type, that didn't work.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question