title: Query MS Access using Excel VBA, SQL BETWEEN dates query
body: <p>all--</p>
  
  <p>I'm attempting to use an SQL query to pull records from an Access db into an Excel VBA userform listbox using the following code:</p>
  
  <pre><code>Sub FillLBBillIDs()
  'build bill ID list box with bill IDs available in database, based on client and/or date range
  '&lt;---------------------------------------------------Dimension all variables
  Dim con As Object, cmd As Object, rst As Object
  Dim Path As String, CName As String
  Dim FromDate As Date, ToDate As Date
  Dim X As Long, Y As Long
  '&lt;---------------------------------------------------Define Default Variables
  X = 0
  CName = AuditParametersFRM.CBOCxName.Value
  FromDate = AuditParametersFRM.DTPFrom.Value
  ToDate = AuditParametersFRM.DTPTo.Value
  '&lt;---------------------------------------------------Define Access connection
  Set con = CreateObject("ADODB.Connection"): Set cmd = CreateObject("ADODB.Command"): Set rst = CreateObject("ADODB.RecordSet"):
  Path = Sheets("AuditTool").Range("B2").Value
  '&lt;---------------------------------------------------Open Access connection
  con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; Path &amp; ";Persist Security Info=False;"
  con.ConnectionTimeout = 0: con.CommandTimeout = 0: con.Open: cmd.CommandTimeout = 0: Set cmd.ActiveConnection = con
  '&lt;---------------------------------------------------Find all bill IDs in the database which match the selected client and
  '&lt;---------------------------------------------------are within the consolidated date range
  rst.Open "SELECT DISTINCT AdHocReport.[BillID] FROM AdHocReport WHERE AdHocReport.[CxName] = '" &amp; CName &amp; "' AND AdHocReport.[ConsolidationDate] BETWEEN #" &amp; FromDate &amp; "# AND #" &amp; ToDate &amp; "#", con, 1, 3
  On Error Resume Next
  rst.MoveLast
  rst.MoveFirst
  Y = 0
  Y = rst.RecordCount
  AuditToolFRM.LBBillIDs.Clear
  If Not Y = 0 Then
      Do Until rst.EOF
  '&lt;---------------------------------------------------Build the listbox with the acquired information
          With AuditToolFRM.LBBillIDs
              .AddItem
              .List(X, 0) = rst![BillID]
              X = X + 1
          End With
          rst.MoveNext
      Loop
  End If
  rst.Close
  On Error GoTo 0
  con.Close
  End Sub
  </code></pre>
  
  <p>This code works just fine if I use a greater than argument, thusly:</p>
  
  <pre><code>rst.Open "SELECT DISTINCT AdHocReport.[BillID] FROM AdHocReport WHERE AdHocReport.[CxName] = '" &amp; CName &amp; "' AND AdHocReport.ConsolidationDate &gt; #" &amp; FromDate &amp; "#", con 1, 3
  </code></pre>
  
  <p>I've gone through all the pertinent questions on this site and can't find anything that works. Any ideas?</p>
  
  <p>Thanks in advance!</p>
  
  <p>12/08/2017 12:54</p>
  
  <p>I've done more testing and it appears that the greater than query isn't working either; it's pulling all records that meet the first criteria whilst ignoring the second, even when using parentheses to enclose the second. This tells me that the issue is definitely in the date portion of the query somehow. Any help is appreciated greatly!</p>