DAO Recordset Filter

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

Guest

I have a very complex query which I call in vba using rst =
db.OpenRecordset("xxx").

I am then trying to filter it to a range of values in one field "[WeekNo]",
but am clearly failing.

What should the next line be to apply the filter to the retrieved recordset?
Note , it will be variable,with values used from stno to fnno.

JMcS
 
JMCS said:
I have a very complex query which I call in vba using rst =
db.OpenRecordset("xxx").

I am then trying to filter it to a range of values in one field
"[WeekNo]", but am clearly failing.

What should the next line be to apply the filter to the retrieved
recordset? Note , it will be variable,with values used from stno to
fnno.
Click to expand...

You have to open a new recordset from the original one, after having set
the original's Filter property. For example,

Set rstUnfiltered = CurrentDb.OpenRecordset("YourQuery")
rstFiltered.Filter = "[WeekNo] > 10 AND [WeekNo] <21"
Set rstFiltered = rstUnfiltered.OpenRecordset
 
This is the perfect textbook answer to the question. Thank you! Pity I
couldn't find it in any of the textbooks I have purchased.

Dirk Goldgar said:
JMCS said:
I have a very complex query which I call in vba using rst =
db.OpenRecordset("xxx").

I am then trying to filter it to a range of values in one field
"[WeekNo]", but am clearly failing.

What should the next line be to apply the filter to the retrieved
recordset? Note , it will be variable,with values used from stno to
fnno.
Click to expand...

You have to open a new recordset from the original one, after having set
the original's Filter property. For example,

Set rstUnfiltered = CurrentDb.OpenRecordset("YourQuery")
rstFiltered.Filter = "[WeekNo] > 10 AND [WeekNo] <21"
Set rstFiltered = rstUnfiltered.OpenRecordset


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
In my first reply, I thanked for providing the code which exactly what I
asked for. Unfortunately, the penultimate line which sets the filter is
causing an "object variable or With blocks variable not set" error.

Here is the code up to the error point. Note that "snno" and "fnno" are
picked up from a form from which the "click" is activated. It falls over
even if I remove reference to stno and fnno, and use your suggested code
exactly:

Private Sub cmdReport_Click()


Dim stDocName As String
Dim stno As Long, fnno As Long
Dim EMail As String
Dim WkNo As Integer
Dim FirstOccur As String
Dim X As Integer
Dim db As DAO.Database
Dim rstUnfiltered As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim names As String



stno = varLong(Me.txtStartWeek.Value)
fnno = varLong(Me.txtEndWeek.Value)


Set db = CurrentDb
Set rstUnfiltered = db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")
rstFiltered.Filter = "[WeekNo] >= stno AND [WeekNo] <= fnno"
Set rstFiltered = rstUnfiltered.OpenRecordset

Dirk Goldgar said:
JMCS said:
I have a very complex query which I call in vba using rst =
db.OpenRecordset("xxx").

I am then trying to filter it to a range of values in one field
"[WeekNo]", but am clearly failing.

What should the next line be to apply the filter to the retrieved
recordset? Note , it will be variable,with values used from stno to
fnno.
Click to expand...

You have to open a new recordset from the original one, after having set
the original's Filter property. For example,

Set rstUnfiltered = CurrentDb.OpenRecordset("YourQuery")
rstFiltered.Filter = "[WeekNo] > 10 AND [WeekNo] <21"
Set rstFiltered = rstUnfiltered.OpenRecordset


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I think Dirk may have made a typo in his reply: you need to instantiated
rstFiltered before you can use it.

Try:

Set db = CurrentDb
Set rstUnfiltered =
db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")
Set rstFiltered = rstUnfiltered.OpenRecordset
rstFiltered.Filter = "[WeekNo] >= " & stno & " AND [WeekNo] <= " & fnno

Note the change I made to your FIlter as well: the variable names have to be
outside of the quotes, or you'll simply get their names, not the values they
contain.

If that doesn't work, you could try:

Set db = CurrentDb
Set rstUnfiltered =
db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")
Set rstFiltered = New DAO.Recordset
rstFiltered.Filter = "[WeekNo] >= " & stno & " AND [WeekNo] <= " & fnno
Set rstFiltered = rstUnfiltered.OpenRecordset


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JMCS said:
In my first reply, I thanked for providing the code which exactly what I
asked for. Unfortunately, the penultimate line which sets the filter is
causing an "object variable or With blocks variable not set" error.

Here is the code up to the error point. Note that "snno" and "fnno" are
picked up from a form from which the "click" is activated. It falls over
even if I remove reference to stno and fnno, and use your suggested code
exactly:

Private Sub cmdReport_Click()


Dim stDocName As String
Dim stno As Long, fnno As Long
Dim EMail As String
Dim WkNo As Integer
Dim FirstOccur As String
Dim X As Integer
Dim db As DAO.Database
Dim rstUnfiltered As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim names As String



stno = varLong(Me.txtStartWeek.Value)
fnno = varLong(Me.txtEndWeek.Value)


Set db = CurrentDb
Set rstUnfiltered = db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")
rstFiltered.Filter = "[WeekNo] >= stno AND [WeekNo] <= fnno"
Set rstFiltered = rstUnfiltered.OpenRecordset

Dirk Goldgar said:
JMCS said:
I have a very complex query which I call in vba using rst =
db.OpenRecordset("xxx").

I am then trying to filter it to a range of values in one field
"[WeekNo]", but am clearly failing.

What should the next line be to apply the filter to the retrieved
recordset? Note , it will be variable,with values used from stno to
fnno.
Click to expand...

You have to open a new recordset from the original one, after having set
the original's Filter property. For example,

Set rstUnfiltered = CurrentDb.OpenRecordset("YourQuery")
rstFiltered.Filter = "[WeekNo] > 10 AND [WeekNo] <21"
Set rstFiltered = rstUnfiltered.OpenRecordset


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Douglas J Steele said:
I think Dirk may have made a typo in his reply: you need to
instantiated rstFiltered before you can use it.

Try:

Set db = CurrentDb
Set rstUnfiltered =
db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")
Set rstFiltered = rstUnfiltered.OpenRecordset
rstFiltered.Filter = "[WeekNo] >= " & stno & " AND [WeekNo] <= "
& fnno
Click to expand...

Oops! I did make a typo, but I don't think you fixed it. It should be:

Set db = CurrentDb

Set rstUnfiltered = _
db.OpenRecordset("qryStaffRequiredTimesheetsLMEMail")

rstUnfiltered.Filter = _
"[WeekNo] >= " & stno & " AND [WeekNo] <= " & fnno

Set rstFiltered = rstUnfiltered.OpenRecordset

You have to set the Filter property of the unfiltered recordset, and
then open the filtered recordset from the unfiltered one.
 
JMCS said:
In my first reply, I thanked for providing the code which exactly
what I asked for. Unfortunately, the penultimate line which sets the
filter is causing an "object variable or With blocks variable not
set" error.
Click to expand...

I goofed. See my reply to Doug Steele in this thread.
 
Back
Top