Setting Query criteria from a record/field

[willyc]

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

[Stewart Ross]

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!

-Stewart

Public Sub UpdateKeywords()
Dim KeywordRst as DAO.Recordset
Dim DataRst as DAO.Recordset
dim Textfield as String
Dim KeyWord as String
set DataRst = CurrentDB.OpenRecordset("MainData")
set KeywordRst = CurrentDB.OpenRecordSet("KeyWords")
Do While not DataRst.EOF
 Textfield = DataRst![ObjectDescription]
 KeyWordRst.MoveFirst
 Do While not KeywordRst.EOF
  KeyWord = KeywordRst![Keyw]
  If Instr(TextField, KeyWord) then
   DataRst.Edit
   DataRst![Cat1] = "X"
   DataRst.Update
  end if
  KeywordRst.Movenext
 Loop
 DataRst.Movenext
Loop
DataRst.Close
KeywordRst.Close
End Sub
 

[willyc]

Many thanks for your reply.

Works a treat!