title: Checkboxes for taking input for a sql query in VBA (userform-excel)
body: <p>I am trying to create a userform - which will have checkboxes for different years and command buttons for different queries. So if for example a user choses three checkboxes -1990,1993, 1995 and then the user clicks on a particular query. Then that query must be executed with that year in going into the "<strong>where</strong> part of that query"
  This is my code :-</p>
  
  <p>*</p>
  
  <pre><code>  Private Sub CommandButton1_Click()
      Dim connection As New ADODB.connection
      Dim rst As New Recordset
      Dim strConnectionStr As String
      Dim Qry As String
      strConnectionStr = "Provider=SQLOLEDB;" &amp; "DATA SOURCE=" &amp; _
                        "INITIAL CATALOG=;" &amp; _
                        "UID=; PWD="
      Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1'group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"
  
      ActiveSheet.Cells.ClearContents
      connection.Open strConnectionStr
      rst.Open Qry, connection
      For iCols = 0 To rst.Fields.Count - 1
          Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
      Next
      Sheets("Query1").Range("A2").CopyFromRecordset rst*`
  
  
      rst.Close
      connection.Close
  
      End Sub
  
  
  Above is the code for normal command buttons without check box . Below is the code for taking user inputs by checkboxes ...
  
      Private Sub CommandButton1_Click()
      Dim connection As New ADODB.connection
      Dim rst As New Recordset
      Dim strConnectionStr As String
      Dim Qry As String
      Dim ctl As Control
      Dim i As Integer
      i = 0
      strConnectionStr = "Provider=SQLOLEDB;" &amp; "DATA SOURCE=;" &amp; _
                        "INITIAL CATALOG=;" &amp; _
                        "UID=; PWD="
      If CheckBox6.Value = True Then
         year1 = CheckBox6.Caption
         i = i + 1
      End If
      If CheckBox5.Value = True Then
         year2 = CheckBox5.Caption
         i = i + 1
      End If
  
      Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1'  and d.Year=CheckBox6.Caption  group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"
  
      ActiveSheet.Cells.ClearContents
  
      connection.Open strConnectionStr
  
      rst.Open Qry, connection
  
      For iCols = 0 To rst.Fields.Count - 1
          Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
      Next
  
      Sheets("Query1").Range("A2").CopyFromRecordset rst
  
  
      rst.Close
      connection.Close
      End Sub
  </code></pre>
  
  <p>ub</p>
  
  <p>Basically I am unable to take values from checkboxes and use them  inside query statement for the purpose.</p>
  
  <p>I need help . Can anyone guide me on this?</p>
  
  <hr>
  
  <p>edit: the following was originally posted as an answer:</p>
  
  <pre><code>If CheckBox1.Value = True Then
   y1 = CheckBox1.Caption
   i = i + 1
  End If
  If CheckBox2.Value = True Then
   y2 = CheckBox2.Caption
   i = i + 1
  End If
  
  If CheckBox3.Value = True Then
  x1 = CheckBox3.Caption
  j = j + 1
  End If
  
  If CheckBox4.Value = True Then
  x2 = CheckBox4.Caption
  j = j + 1
  End If
  
  If CheckBox5.Value = True Then
  x3 = CheckBox5.Caption
  j = j + 1
  End If
  
  If i = 0 Then
  MsgBox "Select at least one year "
  End If
  
  If j = 0 Then
  MsgBox "Select at least one journal "
  End If
  
  
  strConnectionStr = "Provider=SQLOLEDB;" &amp; "DATA SOURCE=;" &amp; _
                    "INITIAL CATALOG=;" &amp; _
                    "UID=; PWD="
  Qry = "  SELECT d.Year,d.[University name],d.[School name], COUNT(distinct d.Title) 'Number of paper published'from [dbo].[Ashish$] d where [Business/Non-business]='1' and "
  Qry = Qry &amp; "[Year] IN (" &amp; y1 &amp; "," &amp; y2 &amp; ") and [Name] IN  (" &amp; x3 &amp; "," &amp; x4 &amp; ") &amp; vbCrLf"
  Qry = Qry &amp; "group by d.Year,d.[University name],d.[School name]  order by d.[Year], [Number of paper published] desc;"
  
  ActiveSheet.Cells.ClearContents
  
  connection.Open strConnectionStr
  
  rst.Open Qry, connection
  
  For iCols = 0 To rst.Fields.Count - 1
      Sheets("Query1").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
  Next
  
  Sheets("Query1").Range("A2").CopyFromRecordset rst
  
  
  rst.Close
  connection.Close
  End Sub
  </code></pre>
  
  <p>Above is my code and the question is posted in my last comment</p>