UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can I Iterate Through A Table Of Strings So That I Can Automate This Update Query?, Access 2016    
 
   
jmkeuning
post Jun 27 2019, 02:45 PM
Post#1



Posts: 140
Joined: 20-October 15



I have a table of text records. I need to search each record for key words, and if the keyword exists, update a field called "Hit" with the term.

So I create a form with one text box. I paste the keyword in the text box and hit the button. That runs this query:

CODE
UPDATE tblMessages SET tblMessages.hit = IIf(IsNull([hit]),[Forms].[frmForm].[Term],[hit] & "; " & [Forms].[frmForm].[Term])
WHERE (((tblMessages.TextBody) Like "*" & [Forms].[frmForm].[Term] & "*")) OR (((tblMessages.From) Like "*" & [Forms].[frmForm].[Term] & "*")) OR (((tblMessages.To) Like "*" & [Forms].[frmForm].[Term] & "*"));


So basically if it finds the term it appends it to the hit field, semicolon delimited.

Is there a way I can point this at a table and have my keywords in a table, and just have this loop through?

Bonus points - when I run the query it tells me that I am about to update n number of records. IT would be amazing if I could update the keyword table with the number of records I updated.

Attached File(s)
Attached File  Clipboard_Image__7_.jpg ( 24.46K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 03:36 PM
Post#2


UtterAccess VIP
Posts: 11,462
Joined: 6-December 03
From: Telegraph Hill


Your Hit field idea is not advisable.

If you had a table of keywords you could just use a query to determine whether the keywords are present:

tblKeywords
ID [PK, auto]
Keyword [Text, Not Null, Indexed (No duplicates)]

Fill with keywords.

Then use something like:
CODE
SELECT
  m.ID,
  m.TextBody,
  k.Keyword
FROM tblMessages m
INNER JOIN tblKeywords k
        ON InStr(m.TextBody, k.Keyword) > 0;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 27 2019, 03:39 PM
Post#3


UtterAccess VIP
Posts: 11,462
Joined: 6-December 03
From: Telegraph Hill


If you need to display a delimited list like you are proposing for your Hit field, you can use a concatenation function such as DBguy's SimpleCSV function.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 12:36 AM