Changing Pivot Table SQL Code

[Dreadknight Nasus]

I'm trying to change the SQL query in my pivot table, with a macro, but I'm getting a 1004 "Application-defined or object-defined error" with the following code:

Private Sub cmndRefresh_Click()


Dim strSQL1 As String


Dim strXSTreaty As String


'Setting Variable value based on XS Treaty Selection
strXSTreaty = cmbxXSTreaty.Value


'Writing SQL code for Pivot Table source data
strSQL1 = "SELECT Pet_Tool_Excel_Export.EVA_DATE, Pet_Tool_Excel_Export.Months, Pet_Tool_Excel_Export.ReserveCategory, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.AccountingName, Pet_Tool_Excel_Export.MCO, Pet_Tool_Excel_Export.PCO, Pet_Tool_Excel_Export.Program, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Company, Pet_Tool_Excel_Export.ASL, Pet_Tool_Excel_Export.ASL2, Pet_Tool_Excel_Export.Assumed, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Year, Pet_Tool_Excel_Export.Active, Pet_Tool_Excel_Export.Captive, Pet_Tool_Excel_Export.Allowance, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Branch, Pet_Tool_Excel_Export.ProgramGroup, Pet_Tool_Excel_Export.INDEX1, Pet_Tool_Excel_Export.MCOPCO,"
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Earned_Premium, Pet_Tool_Excel_Export.Net_Earned_Premium, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Paid_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Incurred_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_Paid_Loss_and_ALAE , Pet_Tool_Excel_Export.Excess_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Paid_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Net_Case_Loss_and_ALAE , Pet_Tool_Excel_Export.Net_Incurred_Loss_and_ALAE, Pet_Tool_Excel_Export.Closed_WithOut_Payment_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Closed_Expense_Only_Claims, Pet_Tool_Excel_Export.Closed_With_Payment_Claims, Pet_Tool_Excel_Export.Open_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Reported_Claims, Pet_Tool_Excel_Export.Gross_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_IBNR_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_ULAE_Reserve, Pet_Tool_Excel_Export.Net_ULAE_Reserve, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Ultimate_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Ultimate_Loss_and_ALAE, "
strSQL1 = strSQL1 + strSQL1 + "Pet_Tool_Excel_Export.Number_of_Closed_Claims_Excluding_Closed_no_pay, Pet_Tool_Excel_Export.Number_of_Reported_Claims_Excluding_Closed_no_pay, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.""QUARTER EVALUATION"", Pet_Tool_Excel_Export.Exclusion, Pet_Tool_Excel_Export.XS_Treaty, Pet_Tool_Excel_Export.CAT_Treaty, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.CLASH_Treaty, Pet_Tool_Excel_Export.SEC_LOB "
strSQL1 = strSQL1 + "FROM PET_Tool.dbo.Pet_Tool_Excel_Export Pet_Tool_Excel_Export "
strSQL1 = strSQL1 + "WHERE (Pet_Tool_Excel_Export.EVA_DATE='12312012') OR (Pet_Tool_Excel_Export.EVA_DATE='03312013') OR (Pet_Tool_Excel_Export.EVA_DATE='06302013') OR (Pet_Tool_Excel_Export.EVA_DATE='09302013') OR (Pet_Tool_Excel_Export.EVA_DATE='12312013') "


'Assigning SQL Code to Pivot Tables
    Sheets("2013 Programs").Select
    ActiveSheet.PivotTables(1).PivotCache.Sql = strSQL1
    
Unload UserForm1 'Closes userform and removes it from memory


End Sub

The red line is where I'm getting the error.

Thanks

 

[Jerry Sullivan]

You are getting an error at the statement that is replacing the query text. That statement itself is fine, so the problem or problems are somewhere within the statements that build that query.

Doing a quick perusal of your query, one problem is that you have an extra "+ strSQL" at about 75% of the way through your query building statements.

strSQL1 = strSQL1 + strSQL1 + "Pet_Tool_Excel_Export.Number_of_Closed_Claims_Excluding_Closed_no_pay...

If after removing that, you still get an error, I'd suggest that you try removing all but 1 fields from the SELECT statement to see if that works.
If so, start adding back 5-6 fields at a time until you can isolate the part(s) that are causing the error.

 

[Dreadknight Nasus]

Okay, thank you, I've identified the problem, which leads me to another one. The issue is that Excel is putting a line break in the middle of StrSQL1, which is messing with the SQL Query. When I take the same code and manually get rid of the line break, it works fine. How do I stop it from doing that?

I tried ending StrSQL1 before the break and assigning the rest of the code to StrSQL2, and then doing ....PivotCache.sql = StrSQL1 + StrSQL2, but it gives me the same result.

Ideas?

 

[Jerry Sullivan]

Where is the unwanted line break occurring?

Typically the evaluation of an SQL expression shouldn't be affected by line feeds if they occur where spaces would be allowed.

 

[Dreadknight Nasus]

strSQL1 = "SELECT Pet_Tool_Excel_Export.EVA_DATE, Pet_Tool_Excel_Export.Months, Pet_Tool_Excel_Export.ReserveCategory, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.AccountingName, Pet_Tool_Excel_Export.MCO, Pet_Tool_Excel_Export.PCO, Pet_Tool_Excel_Export.Program, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Company, Pet_Tool_Excel_Export.ASL, Pet_Tool_Excel_Export.ASL2, Pet_Tool_Excel_Export.Assumed, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Year, Pet_Tool_Excel_Export.Active, Pet_Tool_Excel_Export.Captive, Pet_Tool_Excel_Export.Allowance, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Branch, Pet_Tool_Excel_Export.ProgramGroup, Pet_Tool_Excel_Export.INDEX1, Pet_Tool_Excel_Export.MCOPCO,"
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Earned_Premium, Pet_Tool_Excel_Export.Net_Earned_Premium, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Paid_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Incurred_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_Paid_Loss_and_ALAE , Pet_Tool_Excel_Export.Excess_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Paid_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Net_Case_Loss_and_ALAE , Pe*LineBreak*t_Tool_Excel_Export.Net_Incurred_Loss_and_ALAE, Pet_Tool_Excel_Export.Closed_WithOut_Payment_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Closed_Expense_Only_Claims, Pet_Tool_Excel_Export.Closed_With_Payment_Claims, Pet_Tool_Excel_Export.Open_Claims, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Reported_Claims, Pet_Tool_Excel_Export.Gross_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_IBNR_Loss_and_ALAE, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Excess_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_ULAE_Reserve, Pet_Tool_Excel_Export.Net_ULAE_Reserve, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.Gross_Ultimate_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Ultimate_Loss_and_ALAE, "
strSQL1 = strSQL1 + strSQL1 + "Pet_Tool_Excel_Export.Number_of_Closed_Claims_Excluding_Closed_no_pay, Pet_Tool_Excel_Export.Number_of_Reported_Claims_Excluding_Closed_no_pay, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.""QUARTER EVALUATION"", Pet_Tool_Excel_Export.Exclusion, Pet_Tool_Excel_Export.XS_Treaty, Pet_Tool_Excel_Export.CAT_Treaty, "
strSQL1 = strSQL1 + "Pet_Tool_Excel_Export.CLASH_Treaty, Pet_Tool_Excel_Export.SEC_LOB "
strSQL1 = strSQL1 + "FROM PET_Tool.dbo.Pet_Tool_Excel_Export Pet_Tool_Excel_Export "
strSQL1 = strSQL1 + "WHERE (Pet_Tool_Excel_Export.EVA_DATE='12312012') OR (Pet_Tool_Excel_Export.EVA_DATE='03312013') OR (Pet_Tool_Excel_Export.EVA_DATE='06302013') OR (Pet_Tool_Excel_Export.EVA_DATE='09302013') OR (Pet_Tool_Excel_Export.EVA_DATE='12312013') "

Marked it above in red. It occurs right in the middle of the word 'Pet'.

 

[RoryA]

I don't know if it is related, but I would suggest you use & rather than + when concatenating (it's a risky habit to be in) and also use the CommandText property rather than the deprecated SQL property.

 

[Dreadknight Nasus]

I don't know if it is related, but I would suggest you use & rather than + when concatenating (it's a risky habit to be in) and also use the CommandText property rather than the deprecated SQL property.

Okay, thanks RoryA. I've also managed to get rid of the automatic line break problem by manually putting in line breaks where there are spaces, and the code is still erroring at the same line of code. I also know the SQL code is correct, I checked it by running the code in SQL, so the problem must be in the VBA. I'll try using the CommandText property and fix my code a little bit and then report back.

 

[Dreadknight Nasus]

Okay, I changed it to the CommandText Property and now I'm getting a 1004 Object-defined or application-defined error at the red line:

Private Sub cmndRefresh_Click()


Dim strSQL1 As String


'Writing SQL code for Pivot Table source data
strSQL1 = "SELECT Pet_Tool_Excel_Export.EVA_DATE, Pet_Tool_Excel_Export.Months, Pet_Tool_Excel_Export.ReserveCategory, " & _
    "Pet_Tool_Excel_Export.AccountingName, Pet_Tool_Excel_Export.MCO, Pet_Tool_Excel_Export.PCO, Pet_Tool_Excel_Export.Program, " & _
    "Pet_Tool_Excel_Export.Company, Pet_Tool_Excel_Export.ASL, Pet_Tool_Excel_Export.ASL2, Pet_Tool_Excel_Export.Assumed, " & _
    "Pet_Tool_Excel_Export.Year, Pet_Tool_Excel_Export.Active, Pet_Tool_Excel_Export.Captive, Pet_Tool_Excel_Export.Allowance, " & _
    "Pet_Tool_Excel_Export.Branch, Pet_Tool_Excel_Export.ProgramGroup, Pet_Tool_Excel_Export.INDEX1, Pet_Tool_Excel_Export.MCOPCO, " & _
    "Pet_Tool_Excel_Export.Gross_Earned_Premium, Pet_Tool_Excel_Export.Net_Earned_Premium, " & _
    "Pet_Tool_Excel_Export.Gross_Paid_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_Incurred_Loss_and_ALAE, " & _
    "Pet_Tool_Excel_Export.Excess_Paid_Loss_and_ALAE, Pet_Tool_Excel_Export.Excess_Case_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Paid_Loss_and_ALAE, " & _
    "Pet_Tool_Excel_Export.Net_Case_Loss_and_ALAE, " & vbNewLine
strSQL1 = strSQL1 & "Pet_Tool_Excel_Export.Net_Incurred_Loss_and_ALAE, Pet_Tool_Excel_Export.Closed_WithOut_Payment_Claims, " & _
    "Pet_Tool_Excel_Export.Closed_Expense_Only_Claims, Pet_Tool_Excel_Export.Closed_With_Payment_Claims, Pet_Tool_Excel_Export.Open_Claims, " & _
    "Pet_Tool_Excel_Export.Reported_Claims, Pet_Tool_Excel_Export.Gross_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_IBNR_Loss_and_ALAE, " & _
    "Pet_Tool_Excel_Export.Excess_IBNR_Loss_and_ALAE, Pet_Tool_Excel_Export.Gross_ULAE_Reserve, Pet_Tool_Excel_Export.Net_ULAE_Reserve, " & _
    "Pet_Tool_Excel_Export.Gross_Ultimate_Loss_and_ALAE, Pet_Tool_Excel_Export.Net_Ultimate_Loss_and_ALAE, " & _
    "Pet_Tool_Excel_Export.Number_of_Closed_Claims_Excluding_Closed_no_pay, Pet_Tool_Excel_Export.Number_of_Reported_Claims_Excluding_Closed_no_pay, " & _
    "Pet_Tool_Excel_Export.""QUARTER EVALUATION"", Pet_Tool_Excel_Export.Exclusion, Pet_Tool_Excel_Export.XS_Treaty, Pet_Tool_Excel_Export.CAT_Treaty, " & _
    "Pet_Tool_Excel_Export.CLASH_Treaty, Pet_Tool_Excel_Export.SEC_LOB " & vbNewLine
strSQL1 = strSQL1 & "FROM PET_Tool.dbo.Pet_Tool_Excel_Export Pet_Tool_Excel_Export " & _
    "WHERE (Pet_Tool_Excel_Export.EVA_DATE='12312013')"


'Assigning SQL Code to Pivot Tables
    Sheets("2013 Programs").Select
    
    With ActiveWorkbook.Connections("Query1").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array(strSQL1)
    End With
    
Unload UserForm1 'Closes userform and removes it from memory


End Sub

 

[Dreadknight Nasus]

Even if I try to change the CommandText via the immediate window, using this code

activeworkbook.Connections("Query1").ODBCConnection.CommandText=Array("SELECT Pet_Tool_Excel_Export.EVA_DATE, Pet_Tool_Excel_Export.Months FROM PET_Tool.dbo.Pet_Tool_Excel_Export Pet_Tool_Excel_Export WHERE (Pet_Tool_Excel_Export.EVA_DATE='12312013')")

it gives me the same 1004 error.

I appreciate your guys' help

 

[Jerry Sullivan]

Rory, Thanks as always for your comments. I hadn't used the .SQL property before and just when I learn how to use it I find it's on the way out!

@Dreadknight Nasus,

Have you tried this divide-and-conquer trouble-shooting technique after implementing Rory's suggestions?

If after removing that, you still get an error, I'd suggest that you try removing all but 1 fields from the SELECT statement to see if that works.
If so, start adding back 5-6 fields at a time until you can isolate the part(s) that are causing the error.

I suspect your problem is with: "Pet_Tool_Excel_Export.""QUARTER EVALUATION""
Does your field name really have quotes in it? Even without the quotation marks that reference should be in brackets or single quotes due to the space after QUARTER.