Using IN SQL Operator in Excel

E

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
eoinymc said:
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?
Click to expand...

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

How are you running your SQL?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Do you mean you want to 'inject' the values from worksheet cells into the SQL string?

Like:-
Code: 
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?
 
Upvote 0
You can build an IN clause from a range of worksheet cells like this:-
Code: 
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
 
Upvote 0
I would have to see your code to be sure but assuming your regions are in A1:A10, perhaps something like this:-
Code: 
[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:-
Code: 
[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]
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Guidance Needed: Efficient Data Collection and Liquid Stock Tracking in Excel
Replies
0
Views
126
Azzam
A
T
  • Question Question
VBA to run when a table with a SQL query is refreshed?
Replies
3
Views
259
DickyWest32
D
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
251
cooleomter
C
J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
467
Jared016
J
A
  • Question Question
Changing Source in Power Query using vba
Replies
1
Views
613
JEC
JEC

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top