Add Contents of Cell to VBA Query

Question

I would like to update my code to include the contents of a cell, S3. Right now the cell has the value of 'Test, Testing, Tested' and the relevant part of the query looks like this:

SELECT * FROM dbo.Total WHERE ID = " & TextBox1.Text & " And Source IN (What do I put in here?)

Your help is much appreciated

Answer 1

How about something like this?

Dim someSQL As String
someSQL = "SELECT * FROM dbo.Total WHERE ID = " & TextBox1.Text & _
          " And Source IN (" & Sheet1.Cells(3, 19) & ")"

Substitute the appropriate sheet name for "Sheet1" as needed.

Answer 2

You will need to parse out, trim, escape and quote each of the 3 values in the cell to get:

SELECT * FROM dbo.Total WHERE ID = 'bob''s' And Source IN ('Test','Testing','Tested')

Using

Dim someSQL As String
someSQL = "SELECT * FROM dbo.Total WHERE ID = '" & escape(TextBox1.Text) & "' And Source IN (" & splitCell("s3") & ")"

Function splitCell(addr As String) As String
  Dim v() As String
  Dim i As Long
  v = Split(Range(addr).Value, ",")
  For i = 0 To UBound(v)
      v(i) = "'" & escape(trim$(v(i))) & "'"
  Next
  splitCell = Join(v, ",")
  If (Len(splitCell) = 0) Then splitCell = "''"
End Function

Function escape(sIn As String) As String
  //you should use a prepared statement instead
  escape = Replace$(sIn, "'", "''")
End Function