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

 

[bs0d]

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

Yeah, I got the query to execute without the variable. But when I add it, then it says type mismatch.

heres the code for my variable:

wrkRowCount = 9  'start row    
    Do While wrkRowCount <= 40 'loop until last        
          'txtLeaseList = txtLeaseList & """" & Application.Sheets("MASTER").Range("A" & wrkRowCount).Value & ""","
          txtLeaseList = txtLeaseList & "'" & Application.Sheets("MASTER").Range("A" & wrkRowCount).Value & "',"
        wrkRowCount = wrkRowCount + 1 'increment
    Loop

I've been trying a few different ways of including items in the string. Examples:

each, item, no, quotes
"each", "item", "with", "quotes"
'each', 'item', ...etc.

none of these work.

 

[Andrew Poulsom]

You didn't answer my questions.

 

[bs0d]

You didn't answer my questions.

SQL without the variable (executes successfully) =

        "SELECT LOOKUP.Name, LOOKUP.Type, LOOKUP.Var " _
        , " FROM ABC.LOOKUP LOOKUP" _
        , " WHERE (LOOKUP.Type=3) AND (LOOKUP.Name In ('CAR', 'BIKE', 'SKATE')) " _
        , " ORDER BY LOOKUP.Var")

Variable contains 6 character numbers formatted as text, like: 001122, 334455, 556677 ...

 

[Andrew Poulsom]

I don't see 'Not in' there.

 

[bs0d]

I don't see 'Not in' there.

Yeah, the above is the SQL that works (before adding in any additional conditions to the query). The additional condition adds : AND (LOOKUP.Var Not In (" & txtLeaseList2 & ")) ... but VBA doesn't like this. If I add static values to the additional condition mentioned above, it works too. The problem is when I try to incorporate the string of values.

My variable contains: "001122, 334455, 667788" and so on. So you would think it makese sense you could stick that inside this: Not In ( ) and it would work.

The error changes depending on how I separate each variable within the string. If I include quotes, and ' , then VBA kicks out a Run-time error '13' - Type mismatch.

If I have no " or ' separating them, then VBA kicks out a "General ODBC Error."

 

[Andrew Poulsom]

Can you write a query without variables that uses not in and works?

 

[bs0d]

Yes I can. The variable or how I'm including the variable seems to be the problem. I don't know for sure. Can't crack this one so far.

 

[bs0d]

So far, I think this does the trick:

http://www.mrexcel.com/forum/excel-...perator-inside-visual-basic-applications.html