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]

Difficult to know unless we can see your code...

 

[eoinymc]

Hi Ruddles,

I don't really have any code as I said.

I am using Microsoft Query and am not using any VBA at all.

What I am looking to do is to create a query using VBA which allows parameters in, say, Cells A1:A10 and also a way to update this...

If I give you the following details, could you help me out?

the db details are as follows:
Data Source Name - CATL1
Driver - Microsoft ODBC for Oracle
Description - CATL1
User Name: CATL1_user
Server: LIVE01
Password: CATL1_abc123

Query:
Select * from table1 where region in (1,2,3,4,5,6,7,8,9,10)

The region parameters would be in cells A1:A10.

Can you help me out with this?

Cheers,

Eoin