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......:think:

[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