Help_searching multiple keywords within one field

[2424]

I have a product table and I would like to search for multiple keywords in one field. (short description)

I am having trouble getting a result back from a query that has more than one word placed in the search text box.

The only time I get a result back is when the two keywords are in the same order in the text box "and" the description. For instance, if I type in 'blue car', only the short descriptions with 'blue car' are returned, not 'car blue' does this make sense?

Please help.

Thanks in advance.

Tim

 

[gol4]

You need to pass the or statement and the wild cards in the string.
If you are doing this in the filter for box then you can just use this
*car* or *blue*.
if you want to try this using your own textbox look at the buildcriteria method or parse out the string yourself

 

[2424]

I am a new to access, how do I pass "the or statement and the wild cards" in the string?

 

[gol4]

if you right click on a field a menu pops up

in the white box by filter for: type in

*car* or *blue*

you will see that it returns records that have car or blue any where in the text for that field.

Is this what you had in mind?

 

[2424]

close, but, not exactly.

I want a user to type in a couple of words describing a product into a textbox and have the database return the products that have those keywords somewhere in the short-description field.

For instance, If the user types in one key word (stainless), then all the returned products will have
the word stainless in the short description.

If the user types in two words (stainless steel) or (steel stainless)the database will return all the products with the words "stainless" and "steel" in the short description regardless of the order of the words "stainless" and "steel" that were typed into the textbox.

And so on depending upon how many words they type into the textbox.

Again, thanks for your help

Does this make more sense?

 

[LanceA]

Hi, thank you 2424, I am a limited experience 2000 user, and I had the exact same question. I have a checklist database, and I wanted to have any of our users be able to type in any word from the checklist desription, and have the query return all checklists with that word. The only exception, is then I would like to hyperlink the returns, so if the user selects a checklist, the actual checklist is opened.

Thanks in advance,
Lance

 

[gol4]

2424,

I was afraid you would say that!

I do not plan on writing your code for you but will give you a nudge in which direction you need to head

remember what your are trying to pass to the query is a meaningful string

the criteria would need to look like this

fieldname like "*steel*" or fieldname like "*stainless*"

so how to build that string remembering you want to pass from 1 to many words

Probably the simplest would be using the replace function and since you just want users to pass spaces that will be the delimiter so build your string something like this

txtstr = "*" & replace(me.text1," ","* or *&quot& "*"

you will want to use the trim function to clean it up and such but that should get you started

look at the buildcriteria method it will take this string and build it into proper criteria

strcrit = buildcriteria("fieldname",dbtext,)txtstr)

where before you could call the query you will now need to pass the criteria via vba

something like this will be close

Strsql = select * from mytable where " strcrit

Good Luck

 

[gol4]

Kind of butchered the above

code to build sql string will be
Private Sub Text0_AfterUpdate()
Dim strfilter As String, STRSQL As String
strfilter = BuildCriteria("searchfieldname", 7, "*" & Replace(Me.Text0, " ", "* or *&quot & "*&quot
STRSQL = " SELECT * FROM TABLE1 WHERE " & strfilter
MsgBox STRSQL
End Sub

 

[2424]

Thank you. I appreciate the time and help that you put in to helping me....!

 

[2424]

gol4,

I simply cannot get this thing to work at all. I have tried for mostof the day and continually fail! Anymore help would be appreciated!!!!

Thanks

 

[gol4]

Post what you have so far!

just to make you aware of what I have tested

I created a table with 2 fields
ID shortdesc
1 test
2 this
3 out
4 stainless
5 steel
6 stainless steel

then an unbound form (text0)
I put 1 textbox on it
then 1 unbound listbox (list2)
set its column count to 2
then this code in the after update event

Private Sub Text0_AfterUpdate()
Dim strfilter As String
STRSQL As String
strfilter = BuildCriteria("shortdesc", 7, "*" & Replace(Me.Text0, " ", "* or *&quot & "*&quot
STRSQL = " SELECT * FROM TABLE1 WHERE " & strfilter
Me.List2.RowSource = STRSQL
End Sub

I run the form.
when I type in the textbox
stain steel
I get back records 4 5 and 6

not sure where you are having troubles.
I am using access 2002 but I know the replace function is avaliable from access 2000 on and I used buildcriteria function in access 97

 

[2424]

Thanks again for the response. I will try it. Your help is greatly appreciated. And I do me greatly.

Tim

 

[2424]

I am back. I must be overlooking the easiest thing! It almost seems as if I am not getting the procedure to work. I must not have it set up correctly. I am getting search results the same reults.

So, here is what I have...

Table Name: master_products
From Name: 'Product Search Results' displays search results
Form Name: searchform
On searchform, Textbox Name:textbox
On searchform, Listbox Name:List7
Listbox is set to rowsource type (table/query)
Listbox is set to rowsource (master_products)
Listbox is set to afterupdate to [event procedure]
searchform is also set to the [event procedure]

Private Sub Searchform_AfterUpdate()
Dim strfilter As String
STRSQL As String
strfilter = BuildCriteria("short_desc", 7, "*" & Replace(Me.searchform, " ", "* or *&quot & "*&quot
STRSQL = " SELECT * FROM master_products WHERE " & strfilter
Me.List7.RowSource = STRSQL
End Sub

There is more.

I have a query named 'prodsearch' with the criteria set at
Like "*" & [forms]![searchform]![Textbox] & "*"

I have a macro called 'resultgen' which opens the Product Search Results form to display the results. This macro is activated when the button on the 'searchform' 'On Click'

I hope this information helps describe my problem. Like I said above, the results of my search seem like the are not filtered throught the procudure.

Sorry for the inconvenience.

Tim

 

[gol4]

Several things I see

The code is intended to be in the after update of the textbox. After typing search string and hit enter it will then fire the search.

it gets its data from the textbox not the search form so code needs to be (copy and past into after update of textbox)

Dim strfilter As String
Dim STRSQL As String
strfilter = BuildCriteria("short_desc", 7, "*" & Replace(Me.textbox, " ", "* or *&quot & "*&quot
STRSQL = " SELECT * FROM master_products WHERE " & strfilter
Me.List7.RowSource = STRSQL

this should update the list box with records that meet the criteria in the textbox.

However it appears that is not what you are attempting to do
You are attempting to open the products search form with its row source set to a query whos critera never changes.
the code never changes the value in [forms]![searchform]![Textbox]
if you are going to do it as I am suggesting. I said in an earlier post
"where before you could call the query you will now need to pass the criteria via vba"

Try this

paste the code above into textbox as I suggested then see if it changes the values of the list box.

When and if that works then add this code

docmd.openform "[products search form]"
forms![products search form].form.recordsource = strsql

this will open the form and change the recordsource from the query to the SQL statement

try this and report back
good luck

 

[gcastillo]

I had the same original problem. My approach was to add a field "keyWords" where I put several words and phrases that are related to the product (services, in my case).ie: car,blue,car blue,blue car
I then used a query with LIKE and searched in the ServiceName and keyWord fields, and that worked just fine.

 

[2424]

Well,

I did everything you said and I am getting the following error message...

Microsoft Access can't find the macro 'Dim strfilter As string Dim strsql as string strfilter=buildcriteria("short_desc",7,"*",& Replace(Me.'

Any thoughts as to why I am getting this message?

 

[gol4]

you need to paste in as code in the forms module section

delete the code and click on the 3dots that appear to the far right. In the choose builder form that opens select code builder

paste the code there

also ensure you have the replace function

hit control g int the debugwindow
Paste this in and hit enter
?Replace("test this", " ", "* or *&quot
if it says sub or function not defined you will need to
paste this code in as well


Private Function Replace(StrOrig As String, Optional StrOld As String, Optional StrNew As String) As String
'performs something like excel replace function
Dim i As Integer
SReplace = ""
i = 1
While i <= Len(StrOrig)
SReplace = SReplace & IIf(Mid(StrOrig, i, 1) = StrOld, StrNew, Mid(StrOrig, i, 1))
i = i + 1
Wend
End Function