title: Between two dates in vba for ms access 2010
body: <p>I have a form with multiple combo boxes that opens a report according to the values in my combo boxes.  This works but I was asked to give an option to the Effective Date field so a date range can be entered.  </p>
  
  <p>I added two text boxes (<code>txtReqCreationStartDate</code> and <code>txtReqCreationEndDate</code>) for beginning and ending.  </p>
  
  <p>I tried doing the between...and in the query but then my combo boxes don't work.</p>
  
  <p>I never used SQL but found this online and entered it in the code for the Apply Filter on click button but I get all of the records.  Others have tried to help but I always get all the records.</p>
  
  <p>Here is all of the code for the On Click event.</p>
  
  <pre><code>Private Sub cmdVacanciesWithNoRequisitionParameters_Click()
  On Error GoTo cmdVacanciesWithNoRequisitionParameters_Click_Err
  
  strSQL = "SELECT * FROM qryVacanciesWithNoRequisition WHERE " _
         &amp; "DateValue([Effective Date]) Between #" &amp; Format([txtReqCreationStartDate], "yyyy-mm-dd") &amp; "# And #" &amp; Format([txtReqCreationEndDate], "yyyy-mm-dd") &amp; "#;"
  
  'Person Number
      If Not IsNull(Me.cboPersonNumber) Then
          strFilter = strFilter &amp; " AND [Person Number] Like """ &amp; Me.cboPersonNumber &amp; """ "
      End If
  
               'Person Name
      If Not IsNull(Me.cboPersonName) Then
          strFilter = strFilter &amp; " AND [Person Name] Like """ &amp; Me.cboPersonName &amp; """ "
      End If
  
               'Job Code
      If Not IsNull(Me.cboJobCode) Then
          strFilter = strFilter &amp; " AND [Job Code] Like """ &amp; Me.cboJobCode &amp; """ "
      End If
  
           'Person Number
      If Not IsNull(Me.cboBusinessUnit) Then
          strFilter = strFilter &amp; " AND [Business Unit] Like """ &amp; Me.cboBusinessUnit &amp; """ "
      End If
  
           'Department
      If Not IsNull(Me.cboDepartment) Then
          strFilter = strFilter &amp; " AND [Department] Like """ &amp; Me.cboDepartment &amp; """ "
      End If
  
           'Supervisor
      If Not IsNull(Me.cboSupervisor) Then
          strFilter = strFilter &amp; " AND [Supervisor] Like """ &amp; Me.cboSupervisor &amp; """ "
      End If
  
           'Job Title
      If Not IsNull(Me.cboJobTitle) Then
          strFilter = strFilter &amp; " AND [Job Title] Like """ &amp; Me.cboJobTitle &amp; """ "
      End If
  
              'If the report is closed, open the report
      If SysCmd(acSysCmdGetObjectState, acReport, "rptVacanciesWithNoRequisition") &lt;&gt; acObjStateOpen Then
          DoCmd.OpenReport "rptVacanciesWithNoRequisition", acPreview, qryVacanciesWithNoRequisition
      End If
  
          'if report was open, use filter
      With Reports![rptVacanciesWithNoRequisition]
          .Filter = Mid(strFilter, 6)
          .FilterOn = True
      End With
  
  cmdVacanciesWithNoRequisitionParameters_Click_Exit:
      Exit Sub
  
  cmdVacanciesWithNoRequisitionParameters_Click_Err:
      MsgBox Error$
      Resume cmdVacanciesWithNoRequisitionParameters_Click_Exit
  
  End Sub
  </code></pre>
  
  <p>Thanks in advance for the help.</p>