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

[Cheeky Charlie]

Where and how are you using this? If you're using ms query you can use any sql you like in a pass-through query.

[eoinymc]

Yes, I'm using MS Query...

My question is if I can use IN with a paremeter that is dynamic..

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?

[Cheeky Charlie]

I think you can only do this if you use VBA to construct the SQL string and create the connection. The easiest way of doing this is to record a macro of you creating the database connection (with 'hardcoded parameters') and share the recorded code - we shuld then be able to adjust the code as necessary.

This is a bit harder than normal excel/vba help as there's an awful lot you can't see when someone's using msquery!

[eoinymc]

Thanks Charlie,

Here is the recorded macro (with table names, etc changed...)

Please Login or Register  to view this content.

Any help you can provide will be greatly appreciated!

Cheers,

Eoin

[Cheeky Charlie]

Hi,

Firstly, please put your code in tags:
Click on the edit post button
Click 'go advanced'
Highlight the code
Click the # button
Save
You're new so nobody will get uppity - but we do like our code tags here :/

Assuming you do this - there's a couple of questions that come on the back of this but it looks pretty straightforward:

Do you want to create the connection each time or edit the exisitng query (I would assume the latter, just checking).

How do you want to store your 'choices' - I'm thinking typing in "'1', '2', '3', '4'" isn't very user friendly and will be prone to errors - which SQL doesn't forgive. It's really easy to write a piece of code to create the syntax based on entering 1,2,3,4 (for example).

Quick test would look a bit like:

Please Login or Register  to view this content.

[eoinymc]

Thanks Charlie..

I'm a bit confused on how to use SQL with VBA.

I have only used MS Query previously...

How do I update my statement...how do I create the connection through this?..there are a lot of questions I have regarding this..

Do you have a link showing how to actually create a simple SQL query using VBA?

[Cheeky Charlie]

OK so after you recorded the macro you went to the VB editor to copy the code out and paste it here. Simply replace the code there with what I've put above.

The code as it stands (should) edit the query string of the data connection and refresh it. The string is created here:

Please Login or Register  to view this content.

Which returns:

Please Login or Register  to view this content.

So if you put '1','2','3' in A1 then run this macro it would make the SQL:

Please Login or Register  to view this content.

Probably a good time to upload an example workbook.