A TRUE Keyword Search

[Meep]

Good job, orange. I should be paying you. Would it be possible to automate the population of the unique barcodes table at startup, and then maybe put a "refresh" button on the Reserves Database form itself? I'm not too keen on that separate form.

Also, I noticed that the search can't handle a word containing an apostrophe. Seems to confuse the code. This is a problem because many of my titles begin with the possessive form of the author's last name.

[orange]

Good job, orange. I should be paying you. Would it be possible to automate the population of the unique barcodes table at startup, and then maybe put a "refresh" button on the Reserves Database form itself? I'm not too keen on that separate form.

Also, I noticed that the search can't handle a word containing an apostrophe. Seems to confuse the code. This is a problem because many of my titles begin with the possessive form of the author's last name.

For question 1:
My thinking was you could take the incoming file/data/table and process it into a custom table, process it and return values as appropriate. You could even do it when the Reserves Database form loads. I only put it on a different form to show what steps were involved, and that it had to be done before the Keyword search was invoked. And that was because you wanted only 1 record per ItemBarCode.

Question 2: What flexibility do you have on the keyword or fragment? Nothing says you have to enter a full author's name - any part or break into into 5 parts - doesn't matter. The Title can certainly have a single or double quote/apostophe, and as we can see even an unprintable character or 2.

I test it with things like Ja olg t and if I get too many hits, I add some thing like nt etc.

What is a typical scenario of using the keyword search?
Joe Blow comes to me and says"...."?? What is the process in detail?

[Meep]

You could even do it when the Reserves Database form loads.

That would be good. I might be able to figure that out on my own, but prob not cuz I'm dum.

This database searches the reserves collection of a library. The database is used by a library attendant (not me) to look up the call number of a given title.

So, if the attendant searches for "gray's anatomy," she will get an error. However, you are right, a search for "gray s anatomy" produces the correct results, but the attendant won't know that and will be sad.

[orange]

Let me look at it and see what I need to use a quote? And be aware there are several quotes -- I used to do some data importing from various sources-- and was surprised to find input with 4 different quote representations.( ' " `´)

[orange]

Here's another.
It does the data manipulation stuff to only use 1 ItembarCode per Title, that is empty and refill from dbo_items, as part of Loading the Reserves Database form.

I have also adjusted the code to handle all four variations of quotes.

You can enter a single quote or double or accent grave or accent acute and the keyword code will now handle then all.

The separate form for the data manipulation has been removed.

Test the keyword stuff with
1) 's omy
2) "s omy
3) `s omy

Good luck
orange

also: gray's anatomy DOESN'T EXIST IN THE DATA PROVIDED

try "s omy You'll find

McMinn's clinical atlas of human anatomy
Netter's clinical anatomy
Netter's concise neuroanatomy

[Meep]

This is the one! I'll never bother you again!

[orange]

It's not a bother. Especially if we're all learning.
Good luck and Merry Christmas!

[eliasgirl01]

Essentially, I have a form that a user fills out that enters a record. I would like to search the entered records for keywords in all fields. Since users can update the records and the records can change daily, I'm not sure how to generate a table of keywords that's searchable using the search function you have (I think this is tblKeywords in Meep's case). Unfortunately, I cannot save it in .mdb because I have some multi-valued fields in my form, which I'm afraid will complicate the keyword search.

I also really like the option to create forms/reports.

Please be gentle, I'm an extreme noob

[orange]

I removed the tblKeywords in meep's sample db because he didn't really need it. He was dealing with less than 1000 records.
I adjusted his sample db, as an exercise, to search on multiple fields , not just title.

My recommendation to you is to remove your multivalued fields. They may be a convenience but they are likely to be a source of issues over time. Create normalized tables as necessary.

In the Search_Click event procedure on Form All Fields Search, you can add elements to the array Flds. This is how I set up using several fields in the keyword search. You will see I used

Dim flds(3) As String
flds(0) = "Title"
flds(1) = "Author"
flds(2) = "CurrentStatus"
flds(3) = "DocumentType" to use 4 fields from meep's record layout to be used in the keyword search.

You can adjust to your data as you wish.

I am attaching a zip that has both the Title Search and the All Fields Search.

If you followed this thread with meep, you will see he had some strange (unprintable) characters in his data. We had to process his input into a separate "working" table to remove some duplication.

Good luck.

[eliasgirl01]

Sorry to bug you orange, but I'm confused about 1. normalizing the tables that normally would've been a multi-valued field and 2. the code for the search.

1. To give you more info about my database, a few facts; I'm trying to create a database of testing scripts. Each script record is entered by a user and includes name, description, location, etc. Each script can test multiple applications (this was my multi-valued field). How would I go about replacing this with a normalized table?

2. I used your code, and I allotted up to 7 flds. But I get error 3075; syntax error missing operator in query expression. Then it lists all my flds as null, when they obviously aren't. What am I doing wrong?

I've taken out the multi-valued fields for the moment, and have attached the .mdb that I've created already with only a few records filled in.

[orange]

I have looked at your changes. You have field names with spaces and special characters. These have to be wrapped with [field name with space]. I did this in the Search Click event in the array of field names.
Also, I modified the logic to remove the OR Is Null stuff --- was not needed.
I have attached your mdb with a new name. Give it a try and report back.

Just realized I didn't comment on the normalization. Tell me more about your multivalued fields -- I have acc2003 not 2007 or 2010.

[eliasgirl01]

Wow, this is really awesome, thank you so much. So far its passed a few of my tests and I don't see any problems, but I still need to enter more records.

On my form, there is a drop down list of values for TsuApps. I would like the user to be able to select more than one app from the list. This was the function of my multi-valued fields in my old form via check boxes. Once that record is saved, it should be searchable field just like it is now. Do you know how I could do this?

[orange]

My guess is that you would use a multiselect list box. You just have to get your data into that listbox.
Some info here http://allenbrowne.com/ser-50.html

[cjbnash]

Hi there

This search is great, has anyone got any advice on how to get the results in a listbox?

Thanks

Chris

[gatsby]

orange or anyone who can help me, can u check my database please? i wanted to have the same functions just like yours - can still search an item even the words have been mixed. in your database, it can still search like "clinically spanish (mixed or jumbled)" but in my database it cant. it can only search arrange words like:

condensate export booster pump is the Item Name

condensate export booster pump --- CAN search
pump export condensate booster --- CANNOT search

though theres one good thing with my database, it can search all fields/columns...


attached is my dbs