VBA data type mismatch when blank record exists in MS Access

Question

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.

What am I trying to do?: 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".

query that works if my field column doesnt contain any blanks:

Dim startdate As Date
Dim enddate As Date
strSql = "SELECT Count(*) FROM tablename WHERE datevalue(Date_field_name) >= " & Format(startdate, "\#mm-dd-yyyy\#") & "AND datevalue(Date_field_name) <= " & Format(enddate, "\#mm-dd-yyyy\#")

I tried adding IS NOT NULL and that doesnt work:(

strSql = "SELECT Count(*) FROM tablename WHERE ANOTHER_Field_Name IS NOT NULL AND datevalue(Date_field_name) >= " & Format(startdate, "\#mm-dd-yyyy\#") & "AND datevalue(Date_field_name) <= " & Format(enddate, "\#mm-dd-yyyy\#")

help me pls!! This is driving me mad.

EDIT The complete 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; " & _
    "Data Source=Data.mdb"
strSql = "SELECT Count(*) FROM tablename WHERE datevalue(Date_field_name) >= " & Format(startdate, "\#mm-dd-yyyy\#") & "AND datevalue(Date_field_name) <= " & 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

I don't think you need to do the datevalue thing at all then. — Newd
How are you planning to set the startdate and enddate variables? — Newd

Newd Newd · Accepted Answer · 2015-02-18T16:36:29

I generally see this kind of situation handled with a NZ function so that the field is just never left blank. So something like this:

strSql = "SELECT Count(*) FROM tablename WHERE datevalue(Nz(Date_field_name,"01-01-1969")) >= " & Format(startdate, "\#mm-dd-yyyy\#") & "AND datevalue(Nz(Date_field_name,"01-01-1969")) <= " & Format(enddate, "\#mm-dd-yyyy\#")

This also goes off of the assumption that you are just showing some stripped down code because you declare you variables then run a query without ever changing those variables to be a date.

The "01-01-1969" date should work for what you are looking for but if not adjust accordingly. Also my freehand SQL isn't amazing so some syntax might be a little off.

Correction on code:

strSql = "SELECT Count(*) FROM tablename WHERE datevalue(IIf(IsNull(Date_field_name),"01-01-1969", Date_Field_Name)) >= " & Format(startdate, "\#mm-dd-yyyy\#") & " AND datevalue(IIf(IsNull(Date_field_name),"01-01-1969", Date_Field_Name)) <= " & Format(enddate, "\#mm-dd-yyyy\#")

And if this doesn't suit your fancy if you take a look here: http://www.w3schools.com/sql/sql_isnull.asp There are other alternative ways to deal with nulls.

Round three, I think the Null idea was a red herring, pretty sure it is a lot of the un-needed formatting and that the "Format" part wasn't actually setting it to a date so it wouldn't do the comparison properly. Try out:

strSql = "SELECT Count(*) FROM tablename WHERE Date_field_name >= #" & startdate & "# AND Date_Field_Name <= #" & enddate & “#”

If your enddate and startdate are going to come in a non-date format then use

strSql = "SELECT Count(*) FROM tablename WHERE Date_field_name >= #" & Format(startdate,"\#mm-dd-yyyy\#") & "# AND Date_Field_Name <= #" & Format(enddate,"\#mm-dd-yyyy\#") & “#”

VBA data type mismatch when blank record exists in MS Access

2 Answers