Search by Multiple Keywords from a text field

[NvyAccessUser]

Dear Sir
I've a table called "Table3" with primary key and questions
--------------------
P.K | Question
--------------------
I'd like to search in the questions field using multiple keywords that are not related,
i.e., "population, crowd, ...etc".
My form contains:Text Box(txtDescription), search button(cmdMultiSearch).

I read the post "Search by Multiple Keywords "
and in similar fashon I modified the code to:

Private Sub cmdMultiSearch_Click()
     Dim intIdx As Integer
     Dim strWork As String, strSearch As String
     Dim GCriteria As String    'Unnecessary if Dimensioned elsewhere
 
     If Nz(txtDescription, "") = "" Then
         MsgBox "You must enter a search string."
     Else
         'Generate search criteria
         GCriteria = ""
         strSearch = txtDescription  'txtSearchString
         Do
             intIdx = InStr(1, strSearch, ",")
             If intIdx > 0 Then
                 strWork = Trim(Left(strSearch, intIdx - 1))
                 strSearch = Mid(strSearch, intIdx + 1)
             Else
                 strWork = Trim(strSearch)
                 strSearch = ""
             End If
             GCriteria = GCriteria & " AND ([" & Question & _
                         "] Like '*" & strWork & "*')"
 
         Loop While strSearch > ""
         GCriteria = Mid(GCriteria, 6)
 
         With Me   'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
             'Filter frmReferenceBooks based on search criteria
             .RecordSource = "SELECT * " & _
                             "FROM Table3 " & _
                             "WHERE " & GCriteria
             .Caption = "Table3 (" & _
                        Question & _
                        " contains '" & _
                        txtSearchString & "')"
         End With
         MsgBox "Results have been filtered."
     End If
End Sub

I get the message"Results have been filtered.".
My question is how to get the search result in a table???
Thanks for help.

[NeoPa]

To get the results into a table you need to execute a query very similar to what you have already, but as an append (INSERT INTO) query.

[NvyAccessUser]

Sorry But how to do that because I am new to Access?

[NeoPa]

Hi.

I'd be happy to help further but a more detailed answer requires a much more detailed question.

For instance, which table would you like the data output to? Have you considered how you will use this table? If it's a multi-user situation this is much more involved.

In very basic terms you need SQL of the format :

INSERT INTO [DestinationTable]
          ( [Field1]
          , [Field2] )
SELECT      [Field1]
          , [Field2]
FROM        [SourceTable]
WHERE       ({YourCriteria})

[NvyAccessUser]

Thanks Sir;
It is not multi-user, I did exactly what you advised me but every run I get the whole DB in the DestinationTable.
So is the query syntax wrong or what?
Thanks in advance.

[NeoPa]

From what you say it sounds like the {Criteria} itself is wrong. It would be a good idea, at this stage, to post your SQL (<>VBA) code for us to look over for you.

[NvyAccessUser]

Sorry I mean my SQL statement.
My SQL is:

SELECT Table3.Question, Table3.ID
FROM Table3
WHERE (((Table3.Question) Like  "*"& [Forms]![Main]![txtDescription] & "[!a-z]*"));

but I wand to search for multiple words from the text box and get the result in a table as I click the command button.
Thanks

[NeoPa]

SQL / Query - Both are understood to be the same. I was simply suggesting you avoid posting the VBA code that creates your SQL as many members (unfortunately) do in circumstances where that is not appropriate. You have posted the SQL perfectly (Use the [CODE] tags for preference but the contents are fine).

Back to your questions :
The first problem is that there are two fundamental questions in the question. This is not how things are supposed to work. One question per thread is the rule.

However, in this case there is one fairly simple one which we will get out of the way and allow the thread to remain about matching multiple keywords.

Changing a simple SELECT query into an append (INSERT INTO) query is done by adding the INSERT INTO clause at the front. As illustrated in my (amended) post #4. The INSERT INTO clause should include a list of the fields to take the data within parentheses.

As for the criteria used to handle multiple words, that is much more of a question and will require us to update the SQL code in VBA and execute the amended SQL code from the VBA string. Before we get that far though, we need to find out why what you have isn't working as expected.

First try for me to update the SQL so that each ampersand (&) character is surrounded by at least one space. Test the results with a known value in txtDescription and report back the results and the value used.

[NvyAccessUser]

Thanks Sir, it works fine.

[NeoPa]

Good to hear :-)

Was it the ampersand?