Filter a Query

C

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.
 
M

MGFoster

Carlos said:
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-----
 
A

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?

Carlos said:
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.
 
C

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


MGFoster said:
Carlos said:
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-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top