Type Mismatch adding a 3rd condition to WHERE clause

[Slaghead]

I thought I was done with this but I ended up getting a type mismatch error when I added the customer to the WHERE statement. I can swap out the Item and the Cust filters without any problem but when I have them both included I get the mismatch error.

Set sDate = Sheets("Sheet1").Range("Start_Date")
Set EDate = Sheets("Sheet1").Range("End_Date")
Set Item = Sheets("Sheet1").Range("Items")
Set Cust = Sheets("Sheet1").Range("Customers")
'
    With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:= _
        xlPivotTableVersion12)
        .Connection = Array(Array( _
        "ODBC;DSN=Access;DBQ=C:\Commissions\2011 POS\POS 2011.mdb;DefaultDir=C:\Commissions\2011 POS;DriverId=25;FIL=MS Access;MaxBufferSize=" _
        ), Array("2048;PageTimeout=5;"))
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT YTD_POS.`INDEX NUMBER`, YTD_POS.`POS PARTNER`, YTD_POS.DATE, YTD_POS.ITEM, YTD_POS.QTY, YTD_POS.PRICE, YTD_POS.REVENUE, YTD_POS.CUSTOMER, YTD_POS.CUSTOMER_VARIATION, YTD_POS.SALESREP, YTD_POS.W" _
        , _
        "EEK, YTD_POS.MONTH, YTD_POS.MONTH_CODE, YTD_POS.QUARTER, YTD_POS.YEAR, YTD_POS.INSERT_DATETIME, YTD_POS.NEW_CAT, YTD_POS.TERRITORY, YTD_POS.ZIP_CODE, YTD_POS.`SEGMENTATION 2`, YTD_POS.SEGMENTATION, YT" _
        , _
        "D_POS.`LINE OF BUSINESS`, YTD_POS.`OM CATEGORY NAME`, YTD_POS.STATE, YTD_POS.COUNTRY, YTD_POS.`SHIP TO CUSTOMER`, YTD_POS.`SHIP TO STATE`, YTD_POS.`SHIP TO COUNTRY`, YTD_POS.`INVOICE NUMBER`" & Chr(13) & "" & Chr(10) & "FROM `C:" _
        , _
        "\Commissions\2011 POS\POS 2011.mdb`.YTD_POS YTD_POS" & Chr(13) & "" & Chr(10) & "WHERE (YTD_POS.DATE>=#" & sDate & "# And YTD_POS.DATE<=#" & EDate & "#) AND (YTD_POS.ITEM IN(" & Item & ")) AND (YTD_POS.CUSTOMER_VARIATION IN(" & Cust & "))" _
        )
        .CreatePivotTable TableDestination:="'[POS 2011 Test.xlsx]Sheet1'!R3C1", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12

Thank you in advance for anyone who can point me in the right direction. I am afraid I will have to add a bit of complexity to the WHERE clause to resolve?

 

[Ruddles]

I think it's because Item and Cust are ranges so you can't concatenate them into a string unless they're exactly one cell in size.

To test what I'm saying, make the named ranges Items and Customers each exactly one cell in size and try running the code.

I believe SQL requires the IN list to be provided as a comma-separated list of values, with quotes around them if they are strings.

 

[Slaghead]

Thank you for the response Ruddles.

I used a formula to concatenate the lists so they are csv separated with ' instead of ". Both IN statements work fine independently, it's only when I try to use both of them together that I get this type mismatch error.

But yes, they are both lists, that's why I am using the IN statements instead of just an =. I can try making the strings a single item and change the conditions to = if you think that would help solve my issue, but I really need both items to be strings in order to get to where I need to go.

 

[Richard Schollar]

I reckon your SQL is too long as a result - try creating a string variable to hold it (rather than an array) and assign that to your commandtext property:

Dim strSQL As String

strSQL = "SELECT YTD_POS.`INDEX NUMBER`, YTD_POS.`POS PARTNER`, YTD_POS.DATE, YTD_POS.ITEM, YTD_POS.QTY, YTD_POS.PRICE, YTD_POS.REVENUE, YTD_POS.CUSTOMER, YTD_POS.CUSTOMER_VARIATION, YTD_POS.SALESREP, YTD_POS.WEEK, YTD_POS.MONTH, YTD_POS.MONTH_CODE, YTD_POS.QUARTER, YTD_POS.YEAR, YTD_POS.INSERT_DATETIME, YTD_POS.NEW_CAT, YTD_POS.TERRITORY, YTD_POS.ZIP_CODE, YTD_POS.`SEGMENTATION 2`, YTD_POS.SEGMENTATION, YTD_POS.`LINE OF BUSINESS`, YTD_POS.`OM CATEGORY NAME`, YTD_POS.STATE, YTD_POS.COUNTRY, YTD_POS.`SHIP TO CUSTOMER`, YTD_POS.`SHIP TO STATE`, YTD_POS.`SHIP TO COUNTRY`, YTD_POS.`INVOICE NUMBER` FROM `C:\Commissions\2011 POS\POS 2011.mdb`.YTD_POS YTD_POS WHERE (YTD_POS.DATE>=#" & sDate & "# And YTD_POS.DATE<=#" & EDate & "#) AND (YTD_POS.ITEM IN(" & Item & ")) AND (YTD_POS.CUSTOMER_VARIATION IN(" & Cust & "))" 


'...code

.CommandText = strSQL

If that doesn't work, I would additionally remove all your backticks and replace with square [ ] brackets instead.

 

[Ruddles]

This works fine in Access:-

[FONT=Fixedsys]SELECT YTD_POS.[INDEX NUMBER], YTD_POS.[POS PARTNER], YTD_POS.Date, YTD_POS.ITEM, YTD_POS.QTY, [/FONT]
[FONT=Fixedsys]YTD_POS.PRICE, YTD_POS.REVENUE, YTD_POS.CUSTOMER, YTD_POS.CUSTOMER_VARIATION, YTD_POS.SALESREP, [/FONT]
[FONT=Fixedsys]YTD_POS.WEEK, YTD_POS.Month, YTD_POS.MONTH_CODE, YTD_POS.QUARTER, YTD_POS.Year, [/FONT]
[FONT=Fixedsys]YTD_POS.INSERT_DATETIME, YTD_POS.NEW_CAT, YTD_POS.TERRITORY, YTD_POS.ZIP_CODE, [/FONT]
[FONT=Fixedsys]YTD_POS.[SEGMENTATION 2], YTD_POS.SEGMENTATION, YTD_POS.[LINE OF BUSINESS], YTD_POS.[OM CATEGORY NAME], [/FONT]
[FONT=Fixedsys]YTD_POS.STATE, YTD_POS.COUNTRY, YTD_POS.[SHIP TO CUSTOMER], YTD_POS.[SHIP TO STATE], [/FONT]
[FONT=Fixedsys]YTD_POS.[SHIP TO COUNTRY], YTD_POS.[INVOICE NUMBER][/FONT]
[FONT=Fixedsys]FROM YTD_POS[/FONT]
[FONT=Fixedsys]WHERE [/FONT][FONT=Fixedsys]([/FONT]
[FONT=Fixedsys]YTD_POS.Date>=#12/12/2010# [/FONT]
[FONT=Fixedsys]And YTD_POS.Date<=#1/1/2011# [/FONT]
[FONT=Fixedsys]And YTD_POS.ITEM In (1,4,9,16,25) [/FONT]
[FONT=Fixedsys]And YTD_POS.CUSTOMER_VARIATION In ("Tom","****","Harry")[/FONT]
[FONT=Fixedsys]);[/FONT]

I'm assuming ITEM is numeric and CUSTOMER_VARIATION is text.

If you've still not cracked it, can you Debug.Print the contents of .CommandText and post them here?

 

[Slaghead]

Thank you Richard.

Ok, it appears that it is getting past the .Commandtext array but now it is coming up with an invalid procedure call or argument error when it gets to the .CreatePivotTable argument. But I didn't alter that part of the instruction set.

Set sDate = Sheets("Sheet1").Range("Start_Date")
Set EDate = Sheets("Sheet1").Range("End_Date")
Set Item = Sheets("Sheet1").Range("Items")
Set Cust = Sheets("Sheet1").Range("Customers")
Dim strSQL As String

strSQL = "SELECT YTD_POS.`INDEX NUMBER`, YTD_POS.`POS PARTNER`, YTD_POS.DATE, YTD_POS.ITEM, YTD_POS.QTY, YTD_POS.PRICE, YTD_POS.REVENUE, YTD_POS.CUSTOMER, YTD_POS.CUSTOMER_VARIATION, YTD_POS.SALESREP, YTD_POS.WEEK, YTD_POS.MONTH, YTD_POS.MONTH_CODE, YTD_POS.QUARTER, YTD_POS.YEAR, YTD_POS.INSERT_DATETIME, YTD_POS.NEW_CAT, YTD_POS.TERRITORY, YTD_POS.ZIP_CODE, YTD_POS.`SEGMENTATION 2`, YTD_POS.SEGMENTATION, YTD_POS.`LINE OF BUSINESS`, YTD_POS.`OM CATEGORY NAME`, YTD_POS.STATE, YTD_POS.COUNTRY, YTD_POS.`SHIP TO CUSTOMER`, YTD_POS.`SHIP TO STATE`, YTD_POS.`SHIP TO COUNTRY`, YTD_POS.`INVOICE NUMBER` FROM `C:\Commissions\2011 POS\POS 2011.mdb`.YTD_POS YTD_POS WHERE (YTD_POS.DATE>=#" & sDate & "# And YTD_POS.DATE<=#" & EDate & "#) AND (YTD_POS.ITEM IN(" & Item & ")) AND (YTD_POS.CUSTOMER_VARIATION IN(" & Cust & "))"
    With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:= _
        xlPivotTableVersion12)
        .Connection = Array(Array( _
        "ODBC;DSN=Access;DBQ=C:\Commissions\2011 POS\POS 2011.mdb;DefaultDir=C:\Commissions\2011 POS;DriverId=25;FIL=MS Access;MaxBufferSize=" _
        ), Array("2048;PageTimeout=5;"))
        .CommandType = xlCmdSql
        .CommandText = strSQL
        .CreatePivotTable TableDestination:="'[POS 2011 Test.xlsx]Sheet1'!R3C1", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
    End With

I will try replacing the ' separators with [] and see if that fixes it.

 

[Slaghead]

Thank you again Ruddles.

Both the Item and Customer fields are text actually.

I would love to try sharing the debug information but I'm not sure how and I appear to have lost menu bar in my Excel VBA window.

Also, I tried changing the separators from the ' to the [] and I am still getting the same error when it tries to create the pivot table.

 

[Slaghead]

Ok, well, I at least have the immediate frame in the window still. The SQL string is parsing exactly like I expected.

?strSQL
SELECT YTD_POS.`INDEX NUMBER`, YTD_POS.`POS PARTNER`, YTD_POS.DATE, YTD_POS.ITEM, YTD_POS.QTY, YTD_POS.PRICE, YTD_POS.REVENUE, YTD_POS.CUSTOMER, YTD_POS.CUSTOMER_VARIATION, YTD_POS.SALESREP, YTD_POS.WEEK, YTD_POS.MONTH, YTD_POS.MONTH_CODE, YTD_POS.QUARTER, YTD_POS.YEAR, YTD_POS.INSERT_DATETIME, YTD_POS.NEW_CAT, YTD_POS.TERRITORY, YTD_POS.ZIP_CODE, YTD_POS.`SEGMENTATION 2`, YTD_POS.SEGMENTATION, YTD_POS.`LINE OF BUSINESS`, YTD_POS.`OM CATEGORY NAME`, YTD_POS.STATE, YTD_POS.COUNTRY, YTD_POS.`SHIP TO CUSTOMER`, YTD_POS.`SHIP TO STATE`, YTD_POS.`SHIP TO COUNTRY`, YTD_POS.`INVOICE NUMBER` FROM `C:\Commissions\2011 POS\POS 2011.mdb`.YTD_POS YTD_POS WHERE (YTD_POS.DATE>=#1/1/2011# And YTD_POS.DATE<=#3/6/2011#) AND (YTD_POS.ITEM IN([AB132],[AB135],[AD1142],[],[])) AND (YTD_POS.CUSTOMER_VARIATION IN([ALPHA SYSTEMS],[BACHARAT PROCESSING],[ZERO SUM COMPUTERS],[JACKSON INC],[BANNINATION.COM]))

The .CommandText line looks exactly the same.

 

[Ruddles]

No it isn't:-

AND (YTD_POS.ITEM IN([AB132],[AB135],[AD1142],[],[]))
AND (YTD_POS.CUSTOMER_VARIATION IN([ALPHA SYSTEMS],[BACHARAT PROCESSING],[ZERO SUM COMPUTERS],[JACKSON INC],[BANNINATION.COM]))

The values in your IN list are wrapped in square brackets - they should be wrapped in quotes or apostrophes. SQL thinks they're field names.

 

[Slaghead]

I switched it to brackets per Richard's suggestion but now that I think about it, I think he meant that for the field names and not the filter values. I'll try switching the brackets back.

The strSQL and .CommandText show up the same, not the two IN criteria.

Thanks again, you helped me to reconsider Richard's suggestion. I hope this will work. Wish me luck! brb...