Query Design - Select Items Not in Array

[bs0d]

I need to query table contents where some general items are met, and item id's are not contained in an array.

Example:

SELECT LOOKUP.*, LOOKUP.Var FROM LOOKUP WHERE ((LOOKUP.Name)="test" Or (LOOKUP.Name)="test2" AND ((LOOKUP.Type)=3) AND ((LOOKUP.Var) <> ("array", "of", "items")))

Does this make any sense? I need to know how to add an array to a query since I can't hard-code the list each time, and the array can be a different size each time, so using array variables doesn't make sense here. Thanks

 

[Andrew Poulsom]

Have you tried not in?

SQL: IN Function

 

[bs0d]

Looks like that would work (the not in I guess would be what I'm looking for). I think thats 1/2 the answer. How can I get my array into the SQL statement? Typically I might loop through an array, but I can't in this case.

 

[Andrew Poulsom]

Maybe you can use the Join function.

 

[bs0d]

Ok, I'll look for examples of that function and see if it applies to my situation. Thank you

 

[bs0d]

The plot thickens. I'm getting an error in code, and it's pointing to .Refresh BackgroundQuery:=False. Some how I'm doubting that is the actual error, I suspect it's in my query line. Here is the code I'm using.
(also a quick note - I didn't have the values stored as an array to begin with, so I just stored them as a string with a comma separator to use in the query):

wrkRowCount = varInputs(0)  'holds start row #
    
    Do While wrkRowCount <= varInputs(1) 'loop until last row
        txtLeaseList = txtLeaseList & """" & Application.Sheets("WORKING").Range("A" & wrkRowCount).Value & ""","
        wrkRowCount = wrkRowCount + 1 'increment
    Loop

'remove trailing "," from string:
txtLeaseList2 = Left(txtLeaseList, Len(txtLeaseList) - 1)

'Create tab:
    Sheets.add.Name = "LOOKUP_2"
    ActiveSheet.Move After:=Worksheets(Worksheets.Count) 'move to end

'Query:
    With ActiveSheet.ListObjects.add(SourceType:=0, Source:= _
        "ODBC;DSN=abc;Description=ROI;UID=XXX;APP=2007 Microsoft Office system;WSID=XXX;DATABASE=abc;Trusted_Connection=Yes" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT LOOKUP.* FROM abc.LOOKUP LOOKUP WHERE " _
        , "((LOOKUP.Name) in ('car', 'truck', 'bike', 'skate') " _
        , " AND (LOOKUP.Var) not in (" & txtLeaseList2 & ")" _
        , " AND (LOOKUP.LineType=3.0)" _
        , "ORDER BY LOOKUP.Var")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "LOOKUP_2"
        .Refresh BackgroundQuery:=False
    End With

Any thoughts?

 

[Andrew Poulsom]

I would concentrate on getting the SQL working without any variables first. Then insert the variables and examine it in the Immediate window if it doesn't work.

 

[bs0d]

Ok, that's not a bad idea. I'll see if it runs without it.

 

[bs0d]

Ok, that's not a bad idea. I'll see if it runs without it.

Making progress. Got it to run withouth... added the line for the variable and now a runtime error that says type mismatch, pointing to the line with the varible included in the query...

        , " AND (LOOKUP.Var In (" & txtLeaseList2 & ")) ORDER BY LOOKUP.Var")

 

[Andrew Poulsom]

What was your SQL without variables? And what's assigned to txtLeaseList2?