title: Build VBA Sql statement using 3 multi-select Listboxes and a date range in MS Access 2016 32 bit
body: <p>I have three listboxes and a date range on a parameter form. I am able to pass all of the Listbox selections with no problem. I cannot seem to find a way or answer to adding a date range to the where clause.</p>
  
  <p>The Date field is Course_Date, and the textbox control names for the dates are Start_Date and End_Date</p>
  
  <pre><code>    Option Compare Database
  Option Explicit
  Private Sub cmdPreviewReports_Click()
  On Error GoTo cmdPreviewReports_Err
      Dim blnQueryExists As Boolean
      Dim cat As New ADOX.Catalog
      Dim cmd As New ADODB.Command
      Dim qry As ADOX.View
      Dim varItem As Variant
      Dim strInstructors As String
      Dim strCourseType As String
      Dim strCourseTypeCondition As String
      Dim strRoleType As String
      Dim strRoleTypeCondition As String
      Dim strCourse_Date As Date
      Dim strDateRange As String
      Dim strSql As String
  ' Check for the existence of the stored query
      blnQueryExists = False
      Set cat.ActiveConnection = CurrentProject.Connection
      For Each qry In cat.Views
          If qry.Name = "q_Parameter_Form" Then
              blnQueryExists = True
              Exit For
         End If
      Next qry
  ' Create the query if it does not already exist
      If blnQueryExists = False Then
          cmd.CommandText = "SELECT * FROM q_jt_MCR_Instructor_Roles"
          cat.Views.Append "q_Parameter_Form", cmd
      End If
      Application.RefreshDatabaseWindow
  ' Turn off screen updating
      DoCmd.Echo False
  ' Close the query if it is already open
      If SysCmd(acSysCmdGetObjectState, acQuery, "q_Parameter_Form") = acObjStateOpen Then
          DoCmd.Close acQuery, "q_Parameter_Form"
      End If
  ' Build criteria string for Instructors
      For Each varItem In Me.lst_Instructors.ItemsSelected
          strInstructors = strInstructors &amp; "," &amp; Me.lst_Instructors.ItemData(varItem) &amp; ""
      Next varItem
      If Len(strInstructors) = 0 Then
          strInstructors = "Like '*'"
      Else
          strInstructors = Right(strInstructors, Len(strInstructors) - 1)
          strInstructors = "IN(" &amp; strInstructors &amp; ")"
      End If
  ' Build criteria string for CourseType
      For Each varItem In Me.lst_Course_Type.ItemsSelected
          strCourseType = strCourseType &amp; "," &amp; Me.lst_Course_Type.ItemData(varItem) &amp; ""
      Next varItem
      If Len(strCourseType) = 0 Then
          strCourseType = "Like '*'"
      Else
          strCourseType = Right(strCourseType, Len(strCourseType) - 1)
          strCourseType = "IN(" &amp; strCourseType &amp; ")"
      End If
      ' Get CourseType condition
      If Me.optAndCourseType.Value = True Then
          strCourseTypeCondition = " AND "
      Else
          strCourseTypeCondition = " OR "
      End If
      ' Build criteria string for RoleType
      For Each varItem In Me.lst_Role.ItemsSelected
          strRoleType = strRoleType &amp; "," &amp; Me.lst_Role.ItemData(varItem) &amp; ""
      Next varItem
      If Len(strRoleType) = 0 Then
          strRoleType = "Like '*'"
      Else
          strRoleType = Right(strRoleType, Len(strRoleType) - 1)
          strRoleType = "IN(" &amp; strRoleType &amp; ")"
      End If
      ' Get RoleType condition
      If Me.optAndRoleType.Value = True Then
          strRoleTypeCondition = " AND "
      Else
          strRoleTypeCondition = " OR "
      End If
      'Build Criteria String for Course_Date
          strDateRange = strSql And " Between Me.[Start_Date] AND Me.[End_Date]"
  
  ' Build SQL statement
               strSql = "SELECT q_jt_MCR_Instructor_Roles.* FROM q_jt_MCR_Instructor_Roles " &amp; _
               "WHERE q_jt_MCR_Instructor_Roles.[InstructorID] " &amp; strInstructors &amp; _
               strCourseTypeCondition &amp; "q_jt_MCR_Instructor_Roles.[Course_TypesID] " &amp; strCourseType &amp; _
               strRoleTypeCondition &amp; "q_jt_MCR_Instructor_Roles.[Roles_ID] " &amp; strRoleType &amp; ";"
  ' Apply the SQL statement to the stored query
      cat.ActiveConnection = CurrentProject.Connection
      Set cmd = cat.Views("q_Parameter_Form").Command
      cmd.CommandText = strSql
      Set cat.Views("q_Parameter_Form").Command = cmd
      Set cat = Nothing
  ' Open the Query
    If Not IsNull(cboReports) And cboReports &lt;&gt; "" Then
   DoCmd.OpenReport cboReports, acViewPreview  ' use acNormal to print without preview
   Else
   MsgBox ("Please make a Label selection first from the dropdown list to the left.")
   cboReports.SetFocus
   End If
   cboReports = ""
  
  ' If required the dialog can be closed at this point
  '     DoCmd.Close acForm, Me.Name
   'Restore screen updating
  cmdPreviewReports_Exit:
      DoCmd.Echo True
      Exit Sub
  cmdPreviewReports_Err:
      MsgBox "An unexpected error has occurred." _
          &amp; vbCrLf &amp; "Error Number: " &amp; Err.Number _
          &amp; vbCrLf &amp; "Error Description:" &amp; Err.Description _
          , vbCritical, "Error"
      Resume cmdPreviewReports_Exit
  End Sub
  </code></pre>
  
  <p>I am also able to provide the database to look at if you wish?</p>
  
  <p>Thank you for helping !!!!!!</p>
  
  <p>William</p>