Filter on RecordSet

[jason.teen]

Hi,

I dont seem to be able to get this Filter right on my RecordSet for
some reason.

I have this code:

Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"[Attribute Type] <'Feature' "
& _
"ORDER BY [Component]",
dbOpenDynaset)

this record set is different to something like this for instance.
Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"ORDER BY [Component]",
dbOpenDynaset)

rs.Filter = "[Attribute Type] <'Feature'"
The first one is getting the correct results, but the second one isn't.
but I need the feature to be able to filter out criterias as I go along
when I find out more and more information, and I cannot keep updating
the original SQL.

Any ideas?

Thanks.

[Wayne Gillespie]

On 31 Oct 2006 18:32:35 -0800, ja********@gmail.com wrote:

>Hi,

I dont seem to be able to get this Filter right on my RecordSet for
some reason.

I have this code:

Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"[Attribute Type] <'Feature' "
& _
"ORDER BY [Component]",
dbOpenDynaset)

this record set is different to something like this for instance.
Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"ORDER BY [Component]",
dbOpenDynaset)

rs.Filter = "[Attribute Type] <'Feature'"
The first one is getting the correct results, but the second one isn't.
but I need the feature to be able to filter out criterias as I go along
when I find out more and more information, and I cannot keep updating
the original SQL.

Any ideas?

Thanks.

Once you have set the filter property, you need to open another recordset on the
original to see the results.

rs.Filter = "[Attribute Type] <'Feature'"
Set rsNew = rs.OpenRecordset

rsNew will contain the filtered records.
rs will remain unchanged.

Wayne Gillespie
Gosford NSW Australia

[jason.teen]

Will you also be able to mix and match SQL with recordset?

like for example if after I get my data in a Recrodset then I want to
go
SELECT * FROM rcdset WHERE fieldName="Blah"


Wayne Gillespie wrote:

On 31 Oct 2006 18:32:35 -0800, ja********@gmail.com wrote:

Hi,

I dont seem to be able to get this Filter right on my RecordSet for
some reason.

I have this code:

Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"[Attribute Type] <'Feature' "
& _
"ORDER BY [Component]",
dbOpenDynaset)

this record set is different to something like this for instance.
Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"ORDER BY [Component]",
dbOpenDynaset)

rs.Filter = "[Attribute Type] <'Feature'"
The first one is getting the correct results, but the second one isn't.
but I need the feature to be able to filter out criterias as I go along
when I find out more and more information, and I cannot keep updating
the original SQL.

Any ideas?

Thanks.

Once you have set the filter property, you need to open another recordset on the
original to see the results.

rs.Filter = "[Attribute Type] <'Feature'"
Set rsNew = rs.OpenRecordset

rsNew will contain the filtered records.
rs will remain unchanged.

Wayne Gillespie
Gosford NSW Australia

[jason.teen]

Hey mate,

Thanks for the response but
I now have the problem of its saying that Error 3061: I have too few
parameters...Expect 2
on this line Set rs = rs.OpenRecordset()

I have no idea why its expecting 2 parameters for.
Code Below:
Do While Not rsRef.EOF
AllAttributeNull = True
ChargeTypeEventOnly = True

rs.FindFirst ("[Component] = '" & rsRef![Component] & "'")

While Not rs.NoMatch
If rs![Attribute] <"Null" Then
AllAttributeNull = False
End If

If rs![Charge Type] <"Event" And rs![Charge Type] <>
"Null" Then
ChargeTypeEventOnly = False
End If

rs.FindNext ("[Component] = '" & rsRef![Component] & "'")
Wend

If AllAttributeNull = True Then

If ChargeTypeEventOnly = True Then
'If all attributes are null and only charge type is
event then remove whole component
rs.filter = "rs![Component] <'" & rsRef![Component] &
"'"
Set rs = rs.OpenRecordset()
Else
'If all attributes are null but not only event exists
ie. RC/NRC then display the all Null record
rs.filter = "rs![Component] <'" & rsRef![Component] &
"' OR (rs![Component] = '" & rsRef![Component] & "' AND rs![Charge
Type] = 'Null')"
Set rs = rs.OpenRecordset()
End If

Else
'If attributes exists then simply remove all the Null
atttributes
rs.filter = "rs![Component] <'" & rsRef![Component] & "'
OR (rs![Component] = '" & rsRef![Component] & "' AND rs![Attribute] <>
'Null')"
Set rs = rs.OpenRecordset()
End If

rsRef.MoveNext

Loop

[jason.teen]

It could also be Error 3079: The specified field
'P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1.P1' could refer to more
than one table listed in the from clause of your SQL statement.
But the funny thing is that I dont have any fields even named P1 and
its not using an SQL statement. its using recordset.filter = .....

[salad]

ja********@gmail.com wrote:

Hi,

I dont seem to be able to get this Filter right on my RecordSet for
some reason.

I have this code:

Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"[Attribute Type] <'Feature' "
& _
"ORDER BY [Component]",
dbOpenDynaset)

this record set is different to something like this for instance.

Where's the Where clause? I see the From/Order...no Where...just a
partial statement excluding the Where.

>

Dim rs As Recordset

Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
Component, Attribute, [Attribute Value], [Attribute Type], [Charge
Type] " & _
"FROM [Tbl_Product_VAS] " & _
"ORDER BY [Component]",
dbOpenDynaset)

rs.Filter = "[Attribute Type] <'Feature'"
The first one is getting the correct results, but the second one isn't.
but I need the feature to be able to filter out criterias as I go along
when I find out more and more information, and I cannot keep updating
the original SQL.

Any ideas?

Thanks.

[Wayne Gillespie]

On 31 Oct 2006 23:05:06 -0800, ja********@gmail.com wrote:

>Hey mate,

Thanks for the response but
I now have the problem of its saying that Error 3061: I have too few
parameters...Expect 2
on this line Set rs = rs.OpenRecordset()

Try .OpenRecordset NOT .OpenRecordset() (no brackets)

Once you have set the filter for rs you must open a NEW recordset based on rs.
You are attempting to reopen rs using the filter.

Dim rs as DAO.Recordset
Dim rsFiltered as DAO.Recordset

1. set rs
SET rs=CurrentDB().OpenRecordset(strSQL,dbOpenDynaset)

2. Set The filter
rs.Filter = "Some Filter"

3. Open a NEW recordset using the filter.
SET rsFiltered = rs.OpenRecordset
Wayne Gillespie
Gosford NSW Australia

[Wayne Gillespie]

On 31 Oct 2006 21:25:25 -0800, ja********@gmail.com wrote:

>Will you also be able to mix and match SQL with recordset?

like for example if after I get my data in a Recrodset then I want to
go
SELECT * FROM rcdset WHERE fieldName="Blah"

No you can't mix the two methods.

Either create your initial recordset using a WHERE statement to return only the
required records. (my preferred method)

SELECT * FROM tblSomeTable WHERE ((SomeField)=" & 'Something' & ");"

OR

Create a recordset returning all required records from the table and then use
the .Filter property of the recordset to create separate recordsets of subsets
of data from the initial recordset.

I find very few situations now a days where I would use filtered recordsets, it
is generally much more efficient to use a WHERE clause to limit your recordset
to only the records you need to work with.
Wayne Gillespie
Gosford NSW Australia

[jason.teen]

Okay, so I have changed it to remove the () and now it doesn't ask for
parameters anymore, but the P1, P1, P1, P1 thing still exists.

I have just come across the .Delete method. I am thinking of using
that, but I see in the docos that it only allows you to delete the
current record in the recordset. Is there something where you can
delete all records matching a WHERE type of string condition?

For Example. rs.Delete(Name="Blha")
so that will remove all records with Name which is "Blha"

????

If thats possible then it will make it better than .Filter, then having
to reopen RecordSets. cause that cant be too efficient.

[jason.teen]

Salad, I didn't put the where there, to demostrate that the .Filter()
method could possibly do the same thing.

rs.OpenRecordset(.....)
rs.Filter....

rsNew = rs.OpenRecordset

The filter should act like a "Where" condition.

[Lyle Fairfield]

ja********@gmail.com wrote in news:1162449393.354569.120780
@f16g2000cwb.googlegroups.com:

If thats possible then it will make it better than .Filter, then having
to reopen RecordSets. cause that cant be too efficient.

You could, of course, use ADO, where filter acts in a more inutitive way:

Public Sub Example()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "Select * FROM [Order Details] ORDER BY OrderID"
' -------
' optional
.ActiveConnection = Nothing
' -------
Debug.Print .RecordCount '2155
.Filter = "OrderID 10400 AND OrderID <= 10500"
Debug.Print .RecordCount '258
Debug.Print .Fields("OrderID").Value '10401
.MoveLast
Debug.Print .Fields("OrderID").Value '10500
.Filter = adFilterNone
Debug.Print .Fields("OrderID").Value '10248
End With
End Sub

Access 2003, Northwind.mdb

In general, ADO is much more powerful and has many more features than DAO
which MS has classified as "Obsolete".

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....

[salad]

ja********@gmail.com wrote:

Salad, I didn't put the where there, to demostrate that the .Filter()
method could possibly do the same thing.

rs.OpenRecordset(.....)
rs.Filter....

rsNew = rs.OpenRecordset

The filter should act like a "Where" condition.

You have
"FROM [Tbl_Product_VAS] " & _
"[Attribute Type] <'Feature'

If that works...you're lucky.