title: VBA data type mismatch when blank record exists in MS Access
body: <p>I have the following vba query that works fine if there are no blank records in a particular field that contains dates. The error occurs when a blank exists in a particular record.</p>
  
  <p><strong>What am I trying to do?:</strong> extract a count out of my database between a date range. The startdate and enddate are cell references formatted as "MM/DD/YYYY" and the datebase field containing the dates are formatted as Date/Time, "short date".</p>
  
  <p><strong>query that works if my field column doesnt contain any blanks:</strong></p>
  
  <pre><code>Dim startdate As Date
  Dim enddate As Date
  strSql = "SELECT Count(*) FROM tablename WHERE datevalue(Date_field_name) &gt;= " &amp; Format(startdate, "\#mm-dd-yyyy\#") &amp; "AND datevalue(Date_field_name) &lt;= " &amp; Format(enddate, "\#mm-dd-yyyy\#")
  </code></pre>
  
  <p><strong>I tried adding IS NOT NULL and that doesnt work:(</strong></p>
  
  <pre><code>strSql = "SELECT Count(*) FROM tablename WHERE ANOTHER_Field_Name IS NOT NULL AND datevalue(Date_field_name) &gt;= " &amp; Format(startdate, "\#mm-dd-yyyy\#") &amp; "AND datevalue(Date_field_name) &lt;= " &amp; Format(enddate, "\#mm-dd-yyyy\#")
  </code></pre>
  
  <p>help me pls!! This is driving me mad.</p>
  
  <p><strong>EDIT</strong> The complete code:</p>
  
  <pre><code>Public Sub counter()
  Dim cn As Object
  Dim rs As Object
  Dim strSql As String
  Dim strConnection As String
  Dim myCounter As Variant
  Dim startdate As Date
  Dim enddate As Date
  Set cn = CreateObject("ADODB.Connection")
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " &amp; _
      "Data Source=Data.mdb"
  strSql = "SELECT Count(*) FROM tablename WHERE datevalue(Date_field_name) &gt;= " &amp; Format(startdate, "\#mm-dd-yyyy\#") &amp; "AND datevalue(Date_field_name) &lt;= " &amp; Format(enddate, "\#mm-dd-yyyy\#")
  cn.Open strConnection
  'Set rs = cn.Execute(strSql)
  
  While (rs.EOF = False)
  If (Not IsNull(rs(Sent_To_Tech_Date).Value)) Then
  myCounter = myCounter + 1
  End If
  rs.MoveNext
  Wend
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  End Sub
  </code></pre>