title: Trouble using Between in Select query in ms access 2010 database
body: <p>Morning, please could someone help me get this SELECT query working? I have a form (frm_Reports) upon which I have two text boxes (Text98 and Text100 both formatted to take ShortDate) and a button (RunReport). The user enters a start date in Text98 and an end date in Text100 and then clicks RunReport button. The dates from Text98 and Text100 are stored in variables as Dates (called stardate and enddate) and then a SELECT query (see code below) is meant to run that retrieves all records in a table (tbl_details) that have an entry date (under a column headed DateTime) falling BETWEEN the start date and end date entered by the user. The problem is I cannot get the SELECT query to work. </p>
  
  <p>I am not good at sql type statements so I have tried building my query string a bit at a time testing it as I go. I have managed to get this far successfully:</p>
  
  <p><code>sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) &gt; #" &amp; startdate &amp; "#;</code>"</p>
  
  <p>However, as soon as I try and turn this string into a BETWEEN type query I get </p>
  
  <blockquote>
    <p>"Run-time Error '13': Type mismatch.</p>
  </blockquote>
  
  <pre><code>    Private Sub RunReport_Click()
  
      Dim selectedreport As String
      Dim startdate As Date
      Dim enddate As Date
      Dim sqlstr As String
  
      selectedreport = Me.ComboReport.Column(1)
      startdate = Me.Text98
      enddate = Me.Text100
      'sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) &gt; #28/04/2019#;" - THIS WORKS
      'sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) &gt; #" &amp; startdate &amp; "#;" - THIS WORKS
      sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) BETWEEN #" &amp; startdate &amp; "#" And "#" &amp; enddate &amp; "#;" 'THIS DOES NOT WORK
  
      Dim dbs As Database
      Dim rst As Recordset
  
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset(sqlstr)
      With rst
          While Not .EOF()
          vName = .Fields("DateTime").Value
          Debug.Print vName
  
      .MoveNext
      Wend
      End With
  
      dbs.Close
  
      End Sub
  </code></pre>
  
  <p>I would be very grateful if anyone could show me where I am going wrong with this statement and suggest one that would actually work. Thanks</p>