Filter a Query

[Carlos Mallen]

Hi, I have a query called "Assets" that has the following fields: Company
Name, Account Name and Value. I am trying to filter the query by the Company
Name field as follows:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Assets",dbOpenDynaset)
rst.Filter = "[Company Name] = 'Administaff Inc'"

However, the filter is not working, and I don't know what I'm doing wrong.

 

[MGFoster]

Hi, I have a query called "Assets" that has the following fields: Company
Name, Account Name and Value. I am trying to filter the query by the Company
Name field as follows:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Assets",dbOpenDynaset)
rst.Filter = "[Company Name] = 'Administaff Inc'"

However, the filter is not working, and I don't know what I'm doing wrong.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

From the Access Help article "DAO Filter Property":

Note To see the effects of filtering [rstAssets], you must set its
Filter property, and then open a second Recordset object based on
[rstAssets]. Example:

Set rstAssets = CurrentDB.OpenRecordset("Assets",dbOpenDynaset)
rstAssets.Filter = "[Company Name] = 'Administaff Inc'"
Set rstFilter = rstAssets.OpenRecordset

Note When you know the data you want to select, it's usually more
efficient to create a Recordset with an SQL statement.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZDaooechKqOuFEgEQJIjwCg2i/thqzrGm2kHrGeEROeTkreifwAnAn0
WDxsCUI0mjnKMi7d1BGdBpFe
=LM3y
-----END PGP SIGNATURE-----

 

[AccessVandal via AccessMonster.com]

"Value"? That's a reserved name in Access. Rename it if it's possible.

After the line rst.Filter = "[Company Name] = 'Administaff Inc'" or on
this line did the error appeared or just simply there's no record?

Did you use "Debug.Print rst![Company Name]" the check?

Hi, I have a query called "Assets" that has the following fields: Company
Name, Account Name and Value. I am trying to filter the query by the Company
Name field as follows:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Assets",dbOpenDynaset)
rst.Filter = "[Company Name] = 'Administaff Inc'"

However, the filter is not working, and I don't know what I'm doing wrong.

 

[Carlos Mallen]

MGFoster: I followed your instructions but the record set rstFilter only
contains 1 record, when it should contain 8 (there are 8 records for each
company). I think I'll better open a temporal query and change its SQL.

AccessVandal: The code is not producing any error and the statement

Debug.Print rst![Company Name]

yields "Administaff Inc", which is the first company, but the
rst.RecordCount is still yielding 64 records (8 companies with 8 accounts
each).

Thanks a lot!

--
Carlos

Hi, I have a query called "Assets" that has the following fields: Company
Name, Account Name and Value. I am trying to filter the query by the Company
Name field as follows:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Assets",dbOpenDynaset)
rst.Filter = "[Company Name] = 'Administaff Inc'"

However, the filter is not working, and I don't know what I'm doing wrong.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

From the Access Help article "DAO Filter Property":

Note To see the effects of filtering [rstAssets], you must set its
Filter property, and then open a second Recordset object based on
[rstAssets]. Example:

Set rstAssets = CurrentDB.OpenRecordset("Assets",dbOpenDynaset)
rstAssets.Filter = "[Company Name] = 'Administaff Inc'"
Set rstFilter = rstAssets.OpenRecordset

Note When you know the data you want to select, it's usually more
efficient to create a Recordset with an SQL statement.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZDaooechKqOuFEgEQJIjwCg2i/thqzrGm2kHrGeEROeTkreifwAnAn0
WDxsCUI0mjnKMi7d1BGdBpFe
=LM3y
-----END PGP SIGNATURE-----