Multiple Keyword Search

[gatsby]

i have this database that i cant find solution. its 70-80% done but i cant make it completely working.

my database is about searching an item in a searchbox and once the user search for keyterm/keyword, the listbox below will enumerate or filter the appropriate records. but right now, it can only search/filter keyterm/keyword per field or per column only not the entire field or records.

this is the DBS... actually, i just got that DBS in one of MS Access forums also. i followed and make that DBS my guide in creating my DBS. its almost the same with my DBS:

http://www.access-programmers.co.uk/...93&postcount=3

what i want to do is the DBS to search the entire records either word per word or letter per letter whether its in same field or not.


Example:
i want to search wines with keywords:

Australia (countryname) + semillon (grape) <---- it will give me results

OR....

Aglianico (Region) + Sangiovese (Grape) <------ it will give me results

OR....



Italy (countryname) + Shiraz (Grape) <------- it will give me results

OR...

Shiraz Grove Ashwood (Wine ; written backwards)

because as of now, it can only search keywords that is per field results only. what i want is up 2-5 keywords still it can search an item as long as those letters or word are in the all fields or column.

[June7]

You want to be able to enter all keywords into a single box like a Google internet search? Then the search would match each word across all fields? Never seen anything like that for Access and not finding anything in my Google search. I expect complicated VBA code will be required as well as use of a 'temp' table to store PK of records meeting the criteria.

[stmoong]

Off the top of my head (may not be very efficient), is to search within the concatenated result of those fields that you want to be able to search to be able to return the results.

Something like this:

Query1: SELECT field1, field2, field1 & " " & field2 as concatField FROM table1

Query2: SELECT * FROM Query1 WHERE concatField LIKE '*search term*'

[recyan]

Was thinking along the same lines earlier, but got stuck at

Query2: SELECT * FROM Query1 WHERE concatField LIKE '*search term*'

May be wrong, will the like work ?
The "search term" perhaps contains multiple keywords separated by a space.
We might have to split the "search term" and then search for each of the keyword in the concatenated field.
Had once done it in asp by storing the split keywords in an array and dynamically generating the sql.

Thanks

[June7]

recyan, that is along the lines of what I envision. Turns out the VBA code isn't actually complicated but the entire operation gets tricky. Here is the idea, not tested, just straight out of my head.

Create table RecordMatch with one field called RecordID.

Build query of source records, must have a field that gives the query a unique ID, and each field has the textbox as filter parameter with LIKE "*" and OR operators (stair-steps again).

Build report with a RecordSource that is an SQL statement that joins the query with table by INNER JOIN.

Textbox for input of words separated by space.

Possible code in textbox AfterUpdate or button Click event (only building the array was tested).

CurrentDb.Execute "DELETE FROM RecordMatch;"
Dim aryWords As Variant
Dim i As Integer
aryWords = Split(Me.textboxname)
For i = 0 to UBound(aryWords)
Me.textboxname = aryWords(i)
CurrentDb.Execute "INSERT INTO RecordMatch(RecordID) SELECT ID FROM sourceQuery;"
Next
Me.textboxname = Null
DoCmd.OpenReport "report name"

[recyan]

I had something like below in my asp page :

strsql = " select * from myTable where 1 = 1 and "
    
If Request.Form("searchTerm") <> "" Then 

     Dim strSearchj, arrKeyWordsj, xj
     Dim strWherej, curKeyWordj

     strSearchj=request("searchTerm")
     arrKeyWordsj=Split(strSearchj, ",")

     strWherej=""

     For xj=0 To UBound(arrKeyWordsj)
         curKeyWordj=Replace(arrKeyWordsj(xj), "'", "''")
         If Len(curKeyWordj)>0 Then
            strWherej=strWherej&"myTable.FieldToBeSearchedForKeywords LIKE '%"&curKeyWordj&"%' OR "
         End If
     Next

     If Len(strWherej)>0 Then
        strsql = strsql & " And"
        strWherej=Left(strWherej, Len(strWherej)-Len("OR "))
        strsql=strsql&" ("&strWherej&")"
     End If

End If 

I know how it can be done in an asp page, but do not know how this can be integrated in to VBA & then in to the form or report.

Thanks

[gatsby]

thanks for your inputs, i do appreciate it. but, i have a little knowledge about VBA or other languages. im just a beginner.

if it cant be done, are there any other ways? like creating two or three searchboxes then it will filter down to its specific records? i have tried this in a cascading comboboxes, how i wish they wanted cascading comboboxes in filtering or narrowing down specific records and not searchbox that will search word per word or letter per letter just like the Google as what June7 mentioned.

[June7]

If you want the input to search through all fields, then two or three searchboxes just makes it more complicated.

Method I describe in post 5 is only practical approach I can think of.

[gatsby]

thanks for that but i cant follow post number 5. i have a hard time understanding it but i will try to research more about the idea.

[alcapps]

you are going to need to call a query that looks into each Alphanumeric field you have in your table with an or clause. example..

Table1
field1 numeric
field2 text
field3 text


select * from table1 where
field2 like "*value1*"
or field2 like "*value2*"
or field3 like "*value1*"
or field3 like "*value2*"

you can automate this by using the table and field values in vba.
but if you want to hard code it that is what you can do.
just keep adding or's fieldName like "*values*"

* in a like means a wildcard everything to the right or left of the value you are added to the search.
Try the hard coding first.

then I can give you vba code to pass a table name determine if the fields are alphanumeric and apply the search criteria for a query to it in string.
Then you can run the string to fill a sql where clause and get the results.

Al

[gatsby]

this is really hard. i tried yesterday but i failed. still, it will search per field/column only and not the entire records.

[gatsby]

@alcapps:

im afraid i didnt get what you mean. im sorry but im new to MS Access as well as to VBA. i posted a link here wherein my DBS is the same as that DBS which i got from other forum. can you give me a sample or how it is done basing on that DBS then i will be the one who will finish it? if thats okay with you. Thanks!

[gatsby]

and this is the code for the searchbox:


Private Sub SearchFor_Change()

'Create a string (text) variable
Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchText.Value = vSearchString


'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If

'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
'Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus

If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

End Sub

[alcapps]

I hope this helps. This is just a query based selection method. Table one has flds of text and query1 and query2 pull that data based on search words.

let me know if this is what you are talking about

[gatsby]

thanks. i got ur DBS.

by the way, i have 4,800+ records ; 23 fields .... so its like 4,800 X 23.... i think this example of yours will give me longer time mixing all the words?