MULTIPLE SEARCH 1

[Domino2]

I have been away from Access for 7 years, and boy have I gone rusty.
I am trying to do a search routine that allows words to be entered in a text box separated preferably by commas, that will select those records where those words are contained.

I tried this, but did not get very far.

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Me.List2 = Null

txtSearchString = Trim(Me.Text1.Text & "*")
strSQL = ""
strSQL = "SELECT TABLE1.ID1, TABLE1.Documents FROM Table1 "
strSQL = strSQL & "WHERE ((TABLE1.DOCUMENTS) Like '" & txtSearchString & "*') "
Me.List2.RowSource = strSQL
Me.List2.Requery

First problem I had was not setting focus to Text1, maybe I should have relayed it on to a label?
However even after that, still stuck on syntax to allow multiple words entry to search on.

Appreciate any help. Thanks

 

[dhookom]

In Access, you rarely if ever reference the Text property. Use the Value property or no property since Value is the default.

Also, if Text1 contains multiple values, this will never work. You would need to split the values out and use Like against each of the values.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

Thank you, now changed name and reference to text field name as TXT1. The field TXT1 holds text, a commentary field. I am trying to identify if word/words are contained in the field, if so list the records ID number (Autonumber) in a list box.

Any links to code as a starting reference appreciated

 

[Domino2]

Can someone please tell me what to put in my SQL build statement?

I am trying to recover records where words are within records memo fields. Table name is Table1, My field is called Documents. The table has an autonumber field ID1.

I just want to build a list of record numbers of relevant records. I have searched the forum but just find snippets that don't seem to move me forward. Thank you.

Private Sub Command16_Click()

Dim tbxSearchKeywords As String

Dim keywords() As String

tbxSearchKeywords = "One,Two,Three"

keywords = Split(tbxSearchKeywords, ",")

For Each i In keywords
   MsgBox i
Next i

Dim sql As String
sql = "SELECT * FROM TABLE1 WHERE "

For Each i In keywords
   sql = sql  ' & ..............................
Next i

sql = sql & ";"

  Me.List2.RowSource = sql
  
End Sub

 

[Domino2]

Even this does not work? A query on the table, parameter box opens, I enter a word that I know is in the memo field of one of the records but nothing comes out of the query.

SELECT Table1.ID1, Table1.Documents
FROM Table1
WHERE (((Table1.Documents) ALike "*" & [Enter Keyword] & "*"));

Why is it so easy to enter data in Access, but so hard to get it out?

 

[PHV]

I'd replace ALike with Like

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks PHV. I am working in the query grid of query design, it keeps reverting back to Alike. I will try making the SQL query hardcoded to see what happens

 

[Domino2]

Tried hard coding, but ran into SQL error as foreign language.

Private Sub Command18_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

strSQL = "SELECT Table1.ID1, Table1.Documents FROM Table1 "
strSQL = strSQL & "WHERE (((Table1.Documents) Like " * " & [Enter Keyword] & " * "));"
Me.List2.RowSource = strSQL
End Sub

However I was only trying to just get one word identified in a records memo field just to get started, really losing time because I wanted to get a query working using the split statement to enable several words with separating comma to be looked for in records.

 

[PHV]

Like this ?

...
Dim sql As String
For Each i In keywords
   sql = sql & " OR Documents Like '*" & i & "*'"
Next i
sql = "SELECT * FROM TABLE1 WHERE " & Mid(sql, 5)
Me.List2.RowSource = sql
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks again, however nothing appears in List2.
I have checked the memo fields in some records to see if words exist, and that it's not a case problem.

Dim tbxSearchKeywords As String
Dim sql As String
Dim keywords() As String

tbxSearchKeywords = "One,Two,Three"
keywords = Split(tbxSearchKeywords, ",")

For Each i In keywords
   sql = sql & " OR Documents Like '*" & i & "*'"
Next i
sql = "SELECT * FROM TABLE1 WHERE " & Mid(sql, 5)
Me.List2.RowSource = sql

 

[PHV]

Does List2 have the same number of columns than TABLE1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks
Yes, it has two. The only thing that fills the list is the heading row, with the ID and Documents column.

 

[PHV]

What happens if you replace this:
sql = sql & " OR Documents Like [tt]'*" & i & "*'"[/tt]
with this ?
sql = sql & " OR Documents Like [tt]'[!]%[/!]" & i & "[!]%[/!]'"[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

That's it, my list now fills.

Many many thanks PHV, excellent.

 

[dhookom]

Domino2,
Learn how to set breakpoints and use Debug.Print in your code so you can more easily identify what is happening.

If you can't figure this out then create a small table of keywords:

[pre]
tblKeyWords
===================
KeyWord text
[/pre]

Then allow users to enter individual words into the table. Add this table to a query with your main table and set the filter under the field in the main table like:

[Your Field] Like "*" & KeyWord & "*"

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

Thanks very much, I was just going to build a keyword table, where the user can add to. I was going to display the content in a multi select listbox (simple) so they can pick what they wanted. Just need to find out how one builds up an array useable for the criteria (tbxSearchKeywords = "One,Two,Three"etc)

My other shopping item was, can I display the part of the text held in the memo field (ie 7 words on from the found word) Maybe I am expecting too much. Regards

 

[dhookom]

My suggestion doesn't require an array and the selected words would be easy to display. Not sure if you really have a specific question.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

Understood, thanks.
I just thought I would add another table holding Keywords that a user could store for use and add to.