By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,477 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Setting Query criteria from a record/field

P: 2
Hello all, hopefully you will kind enough to offer some assistance....

I want to make what i believe could be quite a complex query, something beyond the 'basic' access queries. I'm not afraid of VB coding (or programming in general), alas my knowledge of VB syntax is poor so this job is a little beyond my skills at present. But I want to learn.....

Anyway, the exact problem.

For each record in a table (called Maindata) I want to search within a text field (called Objectdescription) and if the field contains any of a list of keywords, update another field (called Cat1) to "X"

That is, I want to flag the Cat1 field with an "X" if the Objectdescription field contains a "keyword" string within the body of text.

Obviously, this is easy enough to do with a basic update query....

My difficulty comes from the keyword.

I have another table (called Keywords) where a list of keywords (in the field Keyw) are stored.

Rather than manually entering each keyword in the query and running it, I want my query to get a keyword with which to test from the Keyw field.

It would be nice if the query then looked up the next keyword and repeated....

My question really boils down to how do I get my query to automatically grab a 'criteria' from my Keywords table?

Many thanks if anyone can assist in this.....
Mar 14 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi. This double-loop VB code processes the keyword file for each entry in the maindata table and updates the Cat1 field if matched. Place the code within a public code module and then call the sub from a command button's on-click event or similar.

As the code uses DAO recordsets you may need to add a reference to the MS DAO object library in your project references. From the VB editor select Tools, References and tick the MS DAO x.x object library to select it. It has been tested on a small dataset and works as expected.

Thanks for providing such a clear explanation of your requirements!

Expand|Select|Wrap|Line Numbers
  1. Public Sub UpdateKeywords()
  2. Dim KeywordRst as DAO.Recordset
  3. Dim DataRst as DAO.Recordset
  4. dim Textfield as String
  5. Dim KeyWord as String
  6. set DataRst = CurrentDB.OpenRecordset("MainData")
  7. set KeywordRst = CurrentDB.OpenRecordSet("KeyWords")
  8. Do While not DataRst.EOF
  9.  Textfield = DataRst![ObjectDescription]
  10.  KeyWordRst.MoveFirst
  11.  Do While not KeywordRst.EOF
  12.   KeyWord = KeywordRst![Keyw]
  13.   If Instr(TextField, KeyWord) then
  14.    DataRst.Edit
  15.    DataRst![Cat1] = "X"
  16.    DataRst.Update
  17.   end if
  18.   KeywordRst.Movenext
  19.  Loop
  20.  DataRst.Movenext
  21. Loop
  22. DataRst.Close
  23. KeywordRst.Close
  24. End Sub
Mar 14 '08 #2

P: 2
Many thanks for your reply.

Works a treat!
Mar 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.