SQL IN statement for multiple columns Excel

Question

Using VBA, Excel 2007, and the following connectionString:

"provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xlsx; Extended Properties=Excel 12.0"

I'm trying to perform the following query SELECT [col1], [col2] FROM [Sheet1$] WHERE ([col1], [col2]) IN ((val1, val2), (val3, val4), (val5, val6))

Alas, VBA errors specifying that it doesn't like the comma in the query expression.

Is there any syntax change I could perform to make this query run? Or another query that would do the same thing?

Below is the surrounding code per request

Sub testQuery()
   Dim conStr As String, sql As String
   conStr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\me\Desktop\QueryMe.xlsx; Extended Properties=Excel 12.0"
   sql = "SELECT col1, col2 FROM [Sheet1$] WHERE (col1, col2) IN (('val1', 'val2'), ('val3', 'val4'), ('val5', 'val6'))"
   Dim objCon As Object, objRS As Object
   Set objCon = CreateObject("ADODB.Connection")
   Set objRS = CreateObject("ADODB.RecordSet")

   objCon.Open con
   objRS.Open sql, con
   objRS.Close
   objCon.Close
End Sub

Have you tried where (col1 = 'val1' and col2='val2') or (col1='val3' and col2='val4') and ...? — zedfoxus
– zedfoxus, Commented May 21, 2015 at 17:39

Joseph · Accepted Answer · 2015-05-21 18:47:29Z

1

You might be able to get away with this:

sql = "SELECT col1, col2 FROM [Sheet1$] WHERE col1+col2 IN ('val1val2','val3val4','val5val6')"

edited May 21, 2015 at 18:47

answered May 21, 2015 at 17:40

Joseph

5,2001 gold badge27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Greg Over a year ago

This query will return different results, such as rows with ('val1', 'val4'), and is not what I'm looking for.

Joseph Over a year ago

@Greg are you looking for combos within the two columns like col1 has both val1' and val2` OR it has both val3 and val4?

Greg Over a year ago

Looking to return only rows which have a listed combination of values. I.e. (col1 = 'val1' AND col2 = 'val2') OR (col1 = 'val3' AND col2 = 'val4') ...

Joseph Over a year ago

@Greg Oh, I see. I updated the answer. I think you might be able to do what I put in the answer. Not completely sure though. Not the most elegant, but if the text is short enough, it might suit you without too much trouble.

Greg Over a year ago

Clever solution. I'd expect this to be slower than zfus' solution? It is certainly a more concise sql statement.

|

Collectives™ on Stack Overflow

SQL IN statement for multiple columns Excel

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related