Search for records with dates

[hometech]

Hello, I have a database through which I want want to be able to search for range of records based on the date criteria. But the challenge I have is that if the starting date does not exist in the database, it gives me an EOF/BOF error.

To illustrate my challenge, lets say i have this database table

ID	Date	comment
1	01/01/14	Blanchard
2	14/01/14	Shall
3	22/02/14	Will
4	10/03/14	Jeff
5	14/03/14	Raymond
6	20/03/14	Bryan
7	26/03/14	Smith

if i enter the search criteria search for records where Date > 10/03/14, I get results from the database

But if i want to search for all records of what happened in march and I say search for records where Date > 01/03/14, i get the EOF/BOF error

See code below

Private Sub showAfter()
frmDate.Show
Dim fPath As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim new_field As ADODB.Field
Dim txt As String
Dim new_range As Range
Dim timer As Date
timer = frmDate.dtpSelect.Value

 ActiveDocument.Tables(3).Cell(1, 3).Range.Text = "Showing all comments before  " & timer
   
    fPath = ThisDocument.Path
   
    Set conn = New ADODB.Connection
    conn.Mode = adModeRead
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & fPath & "\Dbase.mdb"
    conn.Open
   
    Set rs = conn.Execute( _
        "SELECT ID, cmtComment " & _
        "FROM timeComments WHERE cmtDate > # " & timer & " # ORDER BY ID DESC")
        If rs.RecordCount <> 0 Then
        
    txt = txt & rs.GetString( _
        ColumnDelimeter:=vbTab, _
        RowDelimeter:="", _
        NullExpr:="<null>")
   
    rs.Close
    conn.Close
   
    Set new_range = ActiveDocument.Tables(3).Cell(2, 1).Range
   
    With ActiveDocument.Tables(3).Cell(2, 1).Range
    .Delete
    .InsertAfter txt
    End With

    With ActiveDocument.Tables(3).Cell(1, 3).Range
    .Delete
    .Text = "Showing comments before - " & timer
    End With
            Else
    Exit Sub
   End If
End Sub

I'm wondering, is it a syntax/keyword issue??? Just trying to figure out what i'm doing wrong.
Need help guys......

[macropod]

I suggest you check your spelling in the code. There is no such thing as 'ColumnDelimeter' or 'RowDelimeter'.

[hometech]

I suggest you check your spelling in the code. There is no such thing as 'ColumnDelimeter' or 'RowDelimeter'.

Yes i agree with you when if it relates to database, but in the context it is used here, The ColumnDelimeter and RowDelimeter is used to present/align the search result from the database because the output is automatically placed in a table.

[macropod]

My bad - I hadn't realised that the ColumnDelimeter & RowDelimeter misspellings were part of the MS library. Apparently, spelling & spell-checking were not amongst the MS coder's strengths...

FWIW, regarding 'If rs.RecordCount <> 0 Then', to force RecordCount to return the correct value, you should first use MoveLast to force the driver to count the records; otherwise you may end up with -1.

[hometech]

My bad - I hadn't realised that the ColumnDelimeter & RowDelimeter misspellings were part of the MS library. Apparently, spelling & spell-checking were not amongst the MS coder's strengths...

FWIW, regarding 'If rs.RecordCount <> 0 Then', to force RecordCount to return the correct value, you should first use MoveLast to force the driver to count the records; otherwise you may end up with -1.

Yes true but in this database, rs.RecordCount can never be -1, the error only occurs when the record with the particular search criteria does not exist (which is not supposed to be), but if the record with the search criteria exists, it works fine.

[snb]

Sub M_snb()
    c00 = "filename"
    c01 = "tablename"
    c02 = "fieldname"
    c03 = "criterion"
    
    With New ADODB.Recordset
        .Open "Select * from " & c01 & " where [" & c02 & "] = '" & c03 & "'", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & c00
         If .RecordCount <> 0 Then c04 = .GetString(, , "|", vbLf)
        .Close
      End With
      
      If c04 <> "" Then
        sn = Filter(Split(c04, vbLf), "|")
        For j = 1 To UBound(sn)
            sp = Split(sn(j), "|")
            With ActiveDocument.Tables(1)
                .Rows.Add
                y = .Rows.Count
                .Cell(y, 1).Range = sp(0)
                .Cell(y, 2).Range = sp(1)
                .Cell(y, 3).Range = sp(2)
            End With
        Next
     End If
     
     ActiveDocument.Tables(1).Sort True, 2, 2
End Sub