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:
[CODE=sql]SELECT ID, Name, Comments
FROM tblData
WHERE Comments IN ("best","amazin g","George")
ORDER BY ID;[/CODE]

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 "*Conversio n*"

[martin DH]

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

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:
[CODE=sql]SELECT ID, Name, Comments
FROM tblData
WHERE Comments IN ("best","amazin g","George")
ORDER BY ID;[/CODE]

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.
[CODE=vb]
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 rstSearchResult s As DAO.Recordset

'Clear the Results Table (tblResults)
DoCmd.SetWarnin gs False
MySQL = "DELETE * From tblResults;"
DoCmd.RunSQL MySQL
DoCmd.SetWarnin gs True

Set MyDB = CurrentDb()

Set rstKeywords = MyDB.OpenRecord set("tblKeyword s", dbOpenForwardOn ly)
'Will store the actual results
Set rstResults = MyDB.OpenRecord set("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 rstSearchResult s = MyDB.OpenRecord set(strSQLSearc h, dbOpenDynaset)
Do While Not rstSearchResult s.EOF
With rstResults
.AddNew
![ID] = rstSearchResult s![ID]
![Name] = rstSearchResult s![Name]
![Comments] = rstSearchResult s![Comments]
.Update
rstSearchResult s.MoveNext
End With
Loop
rstKeywords.Mov eNext
Loop

'Clean up chores
rstKeywords.Clo se
rstResults.Clos e
rstSearchResult s.Close
Set rstKeywords = Nothing
Set rstResults = Nothing
Set rstSearchResult s = Nothing
[/CODE]