Filter ADO recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have following code that enters the dates into calendar form.I need to
filter some records.I’m aware that I can do it though strSQL but I would
rather filter them.I'm going to need filtered records for a listbox.I'm just
testing it on the calendar to see if it would return only filtered
records.Well, my problem is that it doesn't return filtered records, it
returns all of them. Here is the code:

Dim rcd As ADODB.Recordset, strSQL As String, rcd1 As ADODB.Recordset
Dim cn As ADODB.Connection, strWhat As String
Me!ActiveXCal = Date
strWhat = "Court App." 'Filter

Set cn = CurrentProject.Connection
Set rcd = New ADODB.Recordset

strSQL = " SELECT qvyApp.id,qvyApp.remtime,qvyApp.remETime,qvyApp.Cname, " & _
" qvyApp.remWhat,qvyApp.Applic FROM qvyApp WHERE (qvyApp.remDate) =
date()"
strSQL = strSQL & " ORDER BY remtime"

rcd.Open strSQL, cn
rcd.Filter = "[RemWhat] = '" & strWhat & "'" 'Apply Filter

If rcd.EOF Then
cn.Close
rcd.Close
Set rcd = Nothing
Set cn = Nothing
Exit Sub

Else

Do While Not rcd.EOF
'Enter App. into Calendar
On Error Resume Next
Calendar1.AddAppointment rcd!remtime, rcd!remEtime, rcd!remWhat & ": " &
rcd!CName & " " & rcd!Applic, rcd!ID

rcd.MoveNext
Loop

cn.Close
rcd.Close
Set rcd = Nothing
Set cn = Nothing
end sub

What am I missing?

Thanks in advance

Armin
 
I'm afraid I couldn't read your code, Armin - I find un-indented code
impossible to read - but I tried the following test, and it correctly prints
the names of the only two employees in the Northwind sample database with
birth dates in 1958 ...

Public Sub TestAdoFilter()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open "SELECT LastName, FirstName, BirthDate FROM Employees"
.Filter = "BirthDate >= #1 Jan 1958# AND BirthDate < #1 Jan 1959#"
Do Until .EOF
Debug.Print .Fields("LastName") & ", " & .Fields("FirstName")
.MoveNext
Loop
.Close
End With

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top