Query keywords within text field

[martin DH]

Hello all,

I'm thinking this is a simple question...

I have a table (tblData) that includes a large-capacity text field (255 characters). I would like to write a query that pulls records that have specific keywords within that text field (txtComments).

I've tried the following sql with no success:

SELECT ID, Name, Comments
FROM tblData
WHERE Comments IN ("best","amazing","George")
ORDER BY ID;

eg of a record that would be pulled:
ID
3

Name
John Smith

Comments
Best service ever.

Any ideas out there? Thank you!
martin

[Rabbit]

You're looking for the Like operator. However, if you want to use a variable amount of keywords, you're looking at VBA to rewrite the SQL each time you want to run it.

[martin DH]

You're looking for the Like operator. However, if you want to use a variable amount of keywords, you're looking at VBA to rewrite the SQL each time you want to run it.

I see. The keywords will remain constant (although what they are I have yet to decide). I'm sorry but I'm having a little memory lapse on LIKE.

If I want to pull records that have either "best" and/or "conversion" somewhere in the Comments fields, is this the correct syntax?

Comments LIKE "[best, conversion]"

[Rabbit]

You'll have to use Comments Like "*Best*" OR Comments Like "*Conversion*"

[martin DH]

You'll have to use Comments Like "*Best*" OR Comments Like "*Conversion*"

Right. Thank you, Rabbit.
martin

[Rabbit]

Not a problem, good luck.

[ADezii]

Hello all,

I'm thinking this is a simple question...

I have a table (tblData) that includes a large-capacity text field (255 characters). I would like to write a query that pulls records that have specific keywords within that text field (txtComments).

I've tried the following sql with no success:

SELECT ID, Name, Comments
FROM tblData
WHERE Comments IN ("best","amazing","George")
ORDER BY ID;

eg of a record that would be pulled:
ID
3

Name
John Smith

Comments
Best service ever.

Any ideas out there? Thank you!
martin

I could not find the original code which I created, but I crudely reproduced it below. The original intent was to search Text/Memo Fields for a large number of Keywords which were entered into a Table (tblKeywords). This is accomplished by nested Recordset Loops one for the Keywords and one for matches on each Keyword. The results are then posted to a Table (tblResults). I'll post the code below, and if your are interested or need further information just let me know.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim rstKeywords As DAO.Recordset, MySQL As String
Dim rstResults As DAO.Recordset, strSQLSearch As String
Dim rstSearchResults As DAO.Recordset
 
'Clear the Results Table (tblResults)
DoCmd.SetWarnings False
  MySQL = "DELETE * From tblResults;"
  DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
 
Set MyDB = CurrentDb()
 
Set rstKeywords = MyDB.OpenRecordset("tblKeywords", dbOpenForwardOnly)
'Will store the actual results
Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenTable)
 
 
Do While Not rstKeywords.EOF    'Loop through all the Keywords, find matches below
  strSQLSearch = "Select * From tblData Where [Comments] Like '*" & rstKeywords![Keyword] & "*'"
  Set rstSearchResults = MyDB.OpenRecordset(strSQLSearch, dbOpenDynaset)
    Do While Not rstSearchResults.EOF
      With rstResults
        .AddNew
          ![ID] = rstSearchResults![ID]
          ![Name] = rstSearchResults![Name]
          ![Comments] = rstSearchResults![Comments]
        .Update
        rstSearchResults.MoveNext
      End With
    Loop
      rstKeywords.MoveNext
Loop
 
'Clean up chores
rstKeywords.Close
rstResults.Close
rstSearchResults.Close
Set rstKeywords = Nothing
Set rstResults = Nothing
Set rstSearchResults = Nothing