Using IN SQL Operator in Excel

[eoinymc]

Hi,

I'm pretty sure that you can't use the IN operator when using SQL in Excel, but has anyone got a workaround for this?

I have a query that has 10 "regions" and I want to be able to use a SQL query like the following:

Select * from table
where region in ('1'', '2', '3', '4', '5', '6', '7', '8', '9', '10')
and year = 2011
and month = 1

The regions and year and month will change, so I want to have the query like so:

Select * from table
where region in ('?', '?', '?', '?', '?', '?', '?', '?', '?', '?')
and year = ?
and month = ?

However, when excel get's it's hands on the query, it comes out like this:


Select * from table
where region = ?
and year = ?
and month = ? or
region = ?
and year = ?
and month = ? or
region = ?
and year = ?
and month = ? or
region = ?
and year = ?
and month = ? or
etc....

I have a lot of different queries that are similar and when I am assigning the parameters to each one, it gets very tedious (i.e. 30 parameters in this query).

Is there any way that I can use a range in Excel with this SQL query, or does anyone have any avice that can help me?

If you need anymore information, let me know.

Cheers,

Eoin

 

[Ruddles]

I'm pretty sure that you can't use the IN operator when using SQL in Excel, but has anyone got a workaround for this?

I definitely have SQL queries using ADODB and IN works just fine in those. DAO too I think.

How are you running your SQL?

 

[Fazza]

IN is fine, Eion

Your original
where region in ('1'', '2', '3', '4', '5', '6', '7', '8', '9', '10')

apart from '1'' should be '1'
is good if the field is text

if numeric entries, use
WHERE region IN (1,2,3,4,5,6,7,8,9,10)

If you have data in a table, include that second table in the SQL.

Such as,
SELECT T1.* FROM table1 T1, table2 T2
WHERE T1.Month = T2.Month AND T1.Year = T2.Year

for example

HTH. regards, Fazza

 

[eoinymc]

Yes..IN works fine in the query alright..

but, when I am using it with paramters..

i.e. select * from table where region in ?

And the paremeter is linked to cell A1 for example, which has a value of
'1', '2', '3', '4'....

Is it possible to use it that way?

 

[Ruddles]

Do you mean you want to 'inject' the values from worksheet cells into the SQL string?

Like:-

strSQL = "where region in ([COLOR=red][B]'[/B][/COLOR]" & Range("A1") & "[B][COLOR=red]'[/COLOR][/B] , [COLOR=red][B]'[/B][/COLOR]" & Range ("A2") & "[COLOR=red][B]'[/B][/COLOR])"

Is that what you mean?

 

[Ruddles]

You can build an IN clause from a range of worksheet cells like this:-

Function MakeSQL(rng As Range) As String
 
  Dim oCell As Range
  
  For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
  Next oCell
  
  MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"
  
End Function

 

[eoinymc]

Yes that is what I am looking to do, but how do I put that into my query?

 

[Ruddles]

I would have to see your code to be sure but assuming your regions are in A1:A10, perhaps something like this:-

[FONT=Courier New]Dim strSQL As String[/FONT]
 
[FONT=Courier New]strSQL = "Select * from table where region " & MakeSQL(Range("A1:A10") & " " _[/FONT]
[FONT=Courier New]        "and year = 2011 and month = 1;"[/FONT]

If your year and month are in worksheet cells then you'd drag them in like this:-

[FONT=Courier New]strSQL = "Select * from table where region " & MakeSQL(Range("A1:A10") & " " _[/FONT]
[FONT=Courier New]        "and year = " & Range("B1") & " and month = Range("C1") & ";"[/FONT]

 

[Norie]

How are you creating/using the query?

I think Ruddles has already asked that and I think it might be quite relevant.

I've got a feeling that if you are using something like MS Query that's why you get the conversion you are seeing in.

Perhaps something to do with multiple parameters.

By the way if the values are numbers and really were 1 to 10 you could probably use some other method - even just simple greater than/less than.

 

[eoinymc]

The values are not 1 to 10..I just used that as an example..

I am using MS Query...I can change it though to add the above code..

Has anyone got an explanation or a link telling me how I would add this SQL to my code without using MS Query?

Thanks...