Type mismatch when querying from ODBC table

[mikemny]

Hello All,

I am getting a type mismatch (run time error '13') when I run the below macro, trying to pull data from a database table:

Sub Macro4()
Application.ScreenUpdating = False
Sheets("Returns").Select
Dim ports As String
ports = Range("BA5").Value
 
Range("E6").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _     "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X1234;", _
       Destination:=Range("$E$6")).QueryTable
 
strSQL = "SELECT" _
   & "perf_cust_port_bm_return.portfolio_name," _
   & "perf_cust_port_bm_return.portfolio_full_name," _
   & "perf_cust_port_bm_return.port_mv," _
   & "perf_cust_port_bm_return.mtd_port," _
   & "perf_cust_port_bm_return.mtd_bench," _
   & "perf_cust_port_bm_return.mtd_diff," _
   & "perf_cust_port_bm_return.qtd_port," _
   & "perf_cust_port_bm_return.qtd_bench," _
   & "perf_cust_port_bm_return.qtd_diff," _
   & "perf_cust_port_bm_return.ytd_port," _
   & "perf_cust_port_bm_return.ytd_bench," _
   & "perf_cust_port_bm_return.ytd_diff," _
   & "perf_cust_port_bm_return.begin_dt," _
   & "perf_cust_port_bm_return.end_dt," _
   & "perf_cust_port_bm_return.asof_dt," _
   & "perf_cust_port_bm_return.ov_status" _
& "FROM db_firmprod.dbo.perf_cust_port_bm_return perf_cust_port_bm_return" _
& "GROUP BY" _
   & "perf_cust_port_bm_return.portfolio_name" _
   & "perf_cust_port_bm_return.portfolio_full_name," _
& "HAVING" _
   & (" & ports & ") _
   & "AND (perf_cust_port_bm_return.end_dt={ts '2011-01-31 00:00:00'})" _
   & "AND (perf_cust_port_bm_return.asof_dt={ts '2011-02-14 00:00:00'})"
 
.CommandText = Array(strSQL)
 
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_firm_prod_1"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Table_Query_from_firm_prod_1").TableStyle = _
   "TableStyleLight8"
Range("D5").Select
End Sub

WHAT I'M TRYING TO DO:

There is a table that stores data on many different portfolios (table is called "perf_cust_port_bm_return"). I would like to be able to type the name of the portfolio(s) I would like to see data for, run a macro, and the data is returned to me. The name(s) of the portfolios and the number of portfolios will vary.

I would also like the dates to be variable, although the macro above currently has them set as a particular date. There are two dates needed: an "as of" date, and the most recent previous business day date (the data is posted to the same table daily, and I want to grab the most recent data for that "as of" date).

For the portfolio names, I will list them in column B. For example:
B7 = Portfolio1
B8 = Portfolio2
B9 = Portfolio3

In column BA I have the following formulas set up that produce the following results (I went all the way over to BA so the following formulas will be out of normal viewing, but still easily accessed if something needs to be updated):
BA7 = 'Portfolio1' OR
BA8 = 'Portfolio2' OR
BA9 = 'Portfolio3' OR

In cell BA5, I have a formula that combines the values of BA7 and below, dropping the last "OR":
'Portfolio1' OR 'Portfolio2' OR 'Portfolio3'

The macro above uses the value of BA5.

I have tried many different versions of this macro, but just can't get this to work. I have very limited experience pulling data from database tables, so I appreciate your patience with me on this.

Any help you would be able to provide would be GREATLY appreciated!

Thanks!

 

[Norie]

Sorry, that was kind of a daft question.

It's quite hard to help with these problems - all we can see is the code.

We don't know certain things that might be relevant, for example what data source you are using.

That might be particularly relevant to the error you are getting now because it sounds like it's a problem is with the ODBC connection.

Do you know what the data source is? A database perhaps?

 

[mikemny]

wow! I don't know why, but I needed to have quotes outside of the parenthesis sourounding ports:

& "(" & ports & ")"

It now works. Thank you so much, I would have never been able to figure out all of the spacing issues!