A TRUE Keyword Search

[Meep]

----------

[orange]

Well, there is no data at all in your database, so query3 can't be represented. There's nothing I can try or test unless you can give me some typical test data -- nothing confidential.

[Meep]

There is data now.

(I still want to search query3 "Title" (FirstOfTitle) field, because that table is not my actual table. Thank you.)

[orange]

Just opened your table. Quick question, why are there multiple records of the same
barcode dbo_items ItemBarcode 092274954 092274954

[Meep]

Just opened your table. Quick question, why are there multiple records of the same
barcode dbo_items ItemBarcode 092274954 092274954

Idiosyncrasy of the program used to enter records into table; can't be avoided. Hence my grouping. Does this cause problems when writing the code? If so, we could alternatively group the report (Search Results) to remove duplicates.

[orange]

No, it shows an issue with respect to the cleanliness of the data you are working with.

[orange]

Meep,

I parsed your Titles into a Keywords table, and made a form based on yours. I called the form SearchReserves. It allows you to enter 2 keywords or parts thereof. Open this form to try it.

It's an access2003 mdb, try it and see what you think.

This is only looking at the 500 + titles you supplied.

I have a table called Noise which could be used to remove some keywords from the list to scan.

[Meep]

I appreciate your help, orange, but this doesn't solve my problem, which is that I want the search terms to be entered into a single text box (I won't be the person using this database, and I want it to function like a search engine). With two text boxes I don't understand why parsing the field was even necessary; I could have just used this criterion for the title field: Like "*" & [Forms]![Reserves Database]![TextBox1] & "*" AND Like "*" & [Forms]![Reserves Database]![TextBox2] & "*"

[orange]

Ok, I was think of many many records and a quick index into finding things.

So where are you at at the moment?

[Meep]

So where are you at at the moment?

Still in need of a keyword search! Guus seemed to posit a solution earlier, but I'm too dumb to implement it.

Some dude named John Spencer wrote this in another forum in response to the same problem, but I don't know how to test it:

SELECT *
FROM SomeTable
WHERE SomeField Like "*FirstWord*"
AND SomeField Like "*SecondWord*"

In order to do what you want you need to build the query criteria on the fly.
Dim vArray as Variant
Dim strCon as String
Dim strWhere as String

vArray = Split(Me.txtFilter, " ", -1, 0)

strCon = " AND " 'Must match all words replace with OR to match any

For iLoop = LBound(vArray) To UBound(vArray)

If Len(Trim(vArray(iLoop))) > 0 Then
strWHere= strWhere & _
StrCon & " fSubject Like ""*" & Trim(vArray(iLoop)) & "*"""
End If
Next iLoop


strWhere = Mid(strWhere, Len(strCon) +1)

[orange]

Here's another attempt. It uses your Form and command button now has code behind it to set up the where clause to find records with matching words in the title.

It uses form FoundItems to display results.

Please try it and post back.

John Spencer is well respected and has great knowledge - especially his SQL solutions to issues.

[Meep]

orange, you're my hero! Thank you so much. This seems to work just the way I want it to.

Is it necessary that the search results appear in a form rather than a report? This is purely aesthetic and completely trivial on my part.

[orange]

No, but if there is no Title with, say 6 terms, you don't want to print something that's in error.
Once you have verified that a result was found, you may have to narrow it down. Do you really want to report that these 54 records matched the search terms?

You could have another button on your form to print a report, but I would rerun the search , then click the Report button. We can help you with this, but we don't really know your environment -- you want the search terms/fragments in one text box for example.
Have you received the message that no Title exists with the terms you supplied?

Perhaps you should tell us where this fits in the "big picture".
For example, you have multiple records because they have different callnumbers in many cases.

[Meep]

I'm sorry; I just meant open a report in report view, not print anything. I'm not asking you to redo the whole thing or something, but if it's a matter of changing a word here or there and designing the report, I could do it. Thanks again.

[orange]

What is it exactly that you want to do?
The code behind the button will handle any number of terms/fragments as long as they are separated by one or more spaces. It will open another form if there are Titles matching the search terms OR it will give a message that no Titles exist with all of those terms.
You could close the second form, then click a different button on your form to Preview a Report.

I don't do anything with author or any other fields in dbo_items.

Do you have a report designed?