Confusion on Run Time 13 error

[mikemny]

Hello,

I used the recorder to help write a macro pulling data from a database into excel. I have the following code, which works as written:

ActiveWorkbook.Connections.Add "Query from firm_prod", "", _
        "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X117913;", Array( _
        "SELECT " _
            & "report_field.choice1, report_field.choice2, report_field.choice3, report_field.choice4" _
        & "FROM db_firmprod.dbo.report_field report_field" _
        & "WHERE (report_" _
        , _
        "field.choice1='APPLE') AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) OR " _
            & "(report_field.choice1='OR" _
        , _
        "ANGE') AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) OR " _
            & "(report_field.choice1='PLUM') AND (report_f" _
        , _
        "ield.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'})" _
        & "ORDER BY report_excess_return.choiceID, report_excess_return.asOfDate"), 2

However, when I try to clean up the code, in particular on the "WHERE" line, I get a run time 13 type mismatch error, hilighting the whole section of code listed above. I've tried cleaning up the "WHERE" line as follows:

& "WHERE (report_field.choice1='APPLE') AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) OR (report_field.choice1='ORANGE') AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) OR (report_field.choice1='PLUM') AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'})" _

I need to ultimately make some changes to the code in this line, but need to get rid of the error before I proceed. The macro ultimately displays the results as a pivot table.

Thanks for your help in this, as it is very perplexing to me.

 

[ChrisOswald]

I'm not certain, but I think that the error is caused by the Array() in the command string portion of the line: I've never recorded a database query, and I don't know why the macro recorder would put it in, but in my experience I've always passed SQL as a single string in this type of thing.

I've cleaned up the statement for readability (it appears that there were some missing spaces before some of the keywords): I'm also unsure if the FROM clause is valid.

dim strSQL
strSQL = strSQL & "SELECT "
strSQL = strSQL & "report_field.choice1"
strSQL = strSQL & "report_field.choice2"
strSQL = strSQL & "report_field.choice3"
strSQL = strSQL & "report_field.choice4 "
'This FROM clause looks funny to me...
strSQL = strSQL & "FROM db_firmprod.dbo.report_field report_field "
'This Where clause could probably be simplified down to something like 
'(report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) 
'AND 
'(report_ field.choice1='APPLE' or report_ field.choice1='ORANGE' or report_ field.choice1='PLUM')
'If i'm reading it right, 
strSQL = strSQL & "WHERE (report_ field.choice1='APPLE') "
strSQL = strSQL & "AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) "
strSQL = strSQL & "OR "
strSQL = strSQL & "(report_field.choice1='ORANGE') "
strSQL = strSQL & "AND "
strSQL = strSQL & "(report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) "
strSQL = strSQL & "OR (report_field.choice1='PLUM') "
strSQL = strSQL & "AND (report_field.choice4>={ts '2011-06-30 00:00:00'} And report_field.choice4<={ts '2011-07-28 00:00:00'}) "
strSQL = strSQL & "ORDER BY report_excess_return.choiceID;"
 
ActiveWorkbook.Connections.Add "Query from firm_prod", "", _
        "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X117913;", strSQL

 

[mikemny]

Thanks Chris! I followed your suggestions, and it seems to work!

However, I have a new issue. THe code immediately following this displays the results in a pivot table:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("Query from firm_prod")) _
        .CreatePivotTable TableDestination:=Sheets("Results").Range("C24"), _
        TableName:="Fruit"

Now, when I change the parameters after "WHERE" (for example, changing APPLE, ORANGE, PLUM to being BANANA, GRAPE, KIWI), the results of the pivot table still display APPLE, ORANGE, and PLUM.

Does this have something to do with what is being stored in

 ActiveWorkbook.Connections("Query from firm_prod")

When I query this outside of the macro, it works fine, so I know the data is there.

Thanks again for your help!