title: Using an Excel VBA-defined variable as an "IN" statement of SQL command text
body: <p>I am trying to pass a variable into the command text of a SQL Query in my VBA code, but keep running into a "Type mismatch" error.  I have written a simple equation in my spreadsheet that re-creates the syntax of an IN statement and I just want to plop that into the Query.</p>
  
  <p>So, Cell A1 contains the following:</p>
  
  <pre><code>(1, 2, 3)
  </code></pre>
  
  <p>And the relevant snippets of my VBA are as follows:</p>
  
  <pre><code>Dim x as String
  x = Sheets("SheetName").Range("A1")
  </code></pre>
  
  <p>And then in the long mess of code that is the re-creation of the command text of my query (that I created via the "record" feature), I am trying to inject the variable as follows:</p>
  
  <pre><code>"Where table.field in " &amp; x &amp; " and table2.field &lt;&gt; 0"
  </code></pre>
  
  <p>If it matters:</p>
  
  <ul>
  <li>The field in the table is stored as (int, not null).</li>
  <li>I've used the MsgBox(x) to verify that it is storing the string appropriately</li>
  <li>The string is not greater than the 255 character limit</li>
  <li>I have tried using the standard '?' feature to pass a parameter with MSQuery, also with no luck</li>
  </ul>
  
  <p>I have gotten this kind of trick to work in the past, but only ever with a single variable.  The list of what needs to be put into the IN statement is going to constantly change, so I was just trying to avoid having to call each one out explicitly, or have to return 100% of the data and then filter it out after the fact.</p>
  
  <p>Any help would be appreciated!  Open to other ways of accomplishing the same thing; just had hopes this would work as it is quite easy since the spreadsheet is already set up.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>Working on this again today, I have discovered that I oversimplified the example, I knew the string character limit was not being breached and so I assumed length of the IN clause would be OK.  As is so often the case, I should not have assumed this.</p>
  
  <p>What I have found is that the length of the IN clause is a factor, even when under the 255 limit.  Everything will work fine when the complete length of Cell A1 contains a max of 210 characters; by way of another example:</p>
  
  <pre><code>(1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 19000101, 20160101, 20160530, 20160704, 20160905, 20161124, 20161125, 20161223, 20161226, 20170102, 20170103456789123)
  </code></pre>
  
  <p>If I tack another value onto the end of that last number, to jump the character count up to 211, I will encounter the "Type mismatch" error.</p>
  
  <p>I attempted to avoid this by creating another variable in which to house the remainder of the string, but this too produced the same error.  It would appear if I try to stuff anything over 210 into a single portion of the code, I get the error.</p>
  
  <p>With the assumption that Cell A1 now contains the 210-length version, the below code will execute fine, if A1 contains more than 210 characters, it will give me "Type mismatch":</p>
  
  <pre><code>Dim x As String
  x = Sheets("SheetName").Range("A1")
  
      With ActiveWorkbook.Connections("Query from Database"). _
          ODBCConnection
          .BackgroundQuery = False
          .CommandText = Array( _
          "USE Database" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "SELECT var.UOM, var.UOMClass" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM dbo.Variance var" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE var.UOMC" _
          , "lass in" &amp; x &amp; "and var.ModType &lt;&gt;0")
          .CommandType = xlCmdSql
          .Connection = _
          "ODBC;DSN=Database;UID=user01;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=COM1077;DATABASE=Database"
          .RefreshOnFileOpen = False
          .SavePassword = False
          .SourceConnectionFile = ""
          .SourceDataFile = ""
          .ServerCredentialsMethod = xlCredentialsMethodIntegrated
          .AlwaysUseConnectionFile = False
      End With
      With ActiveWorkbook.Connections("Query from Database")
          .Name = "Query from Database"
          .Description = ""
      End With
      ActiveWorkbook.Connections("Query from Database").Refresh
  </code></pre>
  
  <p>For the moment I have just hard-coded the ~230-length IN statement into the actual command text, and the query runs just fine, but as this list will not only change but grow, I am still curious if a solution exists.</p>