Robust Search Function in Access

[lilanngel]

Hi All,

I'm currently building a database to collect information on a bunch of books. I'm trying to add a search function so that people can find specific books. However, I'm having trouble with the "AND" function.

Essentially, I want people to be able to search for example "twain" and "huckleberry" and still be able to find Mark Twain's Huckleberry Finn. Currently, if they typed in Twain Huckleberry - they would not be able to find anything.

Any ideas?

Thank you!!

Private Sub Search_Cmd_Click()

Dim strLinkCriteria As String
Dim lngLen As Long
'Search
If Not IsNull(Me.SearchAllCBO) Then
strLinkCriteria = strLinkCriteria & "(([Title] Like ""*" & Me.SearchAllCBO & "*"") OR "
strLinkCriteria = strLinkCriteria & "([Author] Like ""*" & Me.SearchAllCBO & "*"") OR "
strLinkCriteria = strLinkCriteria & "([Publication Year] Like ""*" & Me.SearchAllCBO & "*"")) AND "
End If

lngLen = Len(strLinkCriteria) - 5
If lngLen <= 0 Then
Me.FilterOn = False
Else
strLinkCriteria = Left$(strLinkCriteria, lngLen)

Me.Filter = strLinkCriteria
Me.FilterOn = True


End If
End Sub

 

[John Big Booty]

Welcome to the forum.

The sample posted here may be of interest to you.

 

[lilanngel]

Hi there,

Actually... I tried it on my database, and it's not working.

This is because I typed in summaries/abstracts for each book as well, but I can't seem to search through them since I inputted those summaries/abstracts into "Memo" text boxes. Any other way to search through everything?

 

[John Big Booty]

Just had a play with the sample and yes it will search on a Memo Field.

However you do need to ensure that the memo field forms part of of the Query, and that, that filed forms part of the row source for the List box. Have a look at the attached.

Note; This is a very quick bash of the sample and will no longer search on Grape.

 

[Petercheng]

Hi Lilanngel,

While searching the forum, I found your post asking the exactly the same kind of question I want to ask...search book synopsis with key words to return book(s) containing the matched word(s). Very often, more than words will be keyed in to find the books closely related to the key works, like what we do in Google search.
Any success with your inquery and efforts? Can you share your situation with me?
.
Thanks a lot.

Peter

 

[davies107]

i have the same problem with you guys. i wanted my search function to search multiple keywords that is in my database, for example keyword1 + keyword2 + keyword3 = can still search ; even if you mix the keywords can still give results.

please let me know if you solve this problem. thanks a lot!

 

[Petercheng]

davies107,

You are very luck, and I have this problem solved by JANR (he/she is a great guy!!). see this for detail:

http://www.access-programmers.co.uk/forums/showthread.php?p=1197319#post1197319

Good luck
http://www.access-programmers.co.uk/forums/member.php?u=61636

 

[davies107]

its a great database actually but it doesnt apply to mine. John's database got my problem at least but it cannot search multiple keywords, same as your database. i tried your database and searched for :

"pineapple fruit" or "fruit pineapple"

it SHOULD give me a result (second record), but it says "could not find any records that matches the search"

what i am looking is that a database that will give results or records to more keywords searched either in order/disorder or even if you mix it because i have lots of column fields more or less 10.

 

[John Big Booty]

If you want to search multiple search terms in my sample, the easiest way would be to implement a second set search boxes. Otherwise you're in for some complex and interesting coding

 

[davies107]

maybe John, maybe. but i am new to Access and i have very little knowledge about programming/coding, hahaha... im dead! :banghead:

but i wont give up!

 

[John Big Booty]

You would simply need to duplicate everything that has been presented in the sample and adjust the query accordingly..

The code has plenty of explanation in the comments so possibly a good place to dip your toe in the coding pool

 

[davies107]

thats what im going to do. but if ever you will revise your sample database in accordance to my question please let me know

 

[John Big Booty]

The way I would do this would be to start with a fresh Sandbox DB into which I'd import my tables. Then build the search form and get the first search box working to my satisfaction, then simply add my second search box and work from there.

 

[John Big Booty]

One thing you will need to consider is will your treat the two search terms as being joined by the boolean operators of AND or OR.

Possibly you could control this with an option group, but I would start by making it static until you have a good grasp of how the coding is working.

 

[davies107]

thanks a lot John. its my pleasure to follow you here because i could get some ideas from you. i hope ill get this suggestion of yours in a month, haha... wish me luck dude!

 

[John Big Booty]

davies107, I've just spent an hour or so playing around with the Multisearch, and come to the conclusion that it's really not amenable to searching on multiple search terms. Have a look at the attached sample and you will see what I mean. It really requires a completely different approach.

 

[davies107]

thanks John. i tried your sample several times and i believe you are right, it really needs a new approach... sigh ***

 

[gemma-the-husky]

another way is to use a function

function includethis(searchtext as string) as boolean
'put code in here to test whether includethis returns true or false
end function

the problem here us that the function gets evaluated for every record in the data set - so it may take a while to run. it's an easier idea than trying to build a very complicated "where" expression.

the other soultion is maybe a pettern matching approach, although the syntax would be beyond me.

 

[davies107]

thanks Dave and Gemma (your dog, wink)... i cant still figure this out but will try to make this run. sorry if i cant share or interact ideas about programming or even MS-Access because im just new with this stuff.

 

[davies107]

Just had a play with the sample and yes it will search on a Memo Field.

However you do need to ensure that the memo field forms part of of the Query, and that, that filed forms part of the row source for the List box. Have a look at the attached.

Note; This is a very quick bash of the sample and will no longer search on Grape.

Hello John,

just want to ask if its possible to place a SEARCH button in your dynamic search database? i am using your database actually, but it slows down when it has lots of data already especially if it deals with calculation. what i want to do is that whenever you type (letter per letter, word per word), it will not search first, the database lets you continue to finish the word/s then the SEARCH button should be press after youre done and wanted to see the result. i just wanted to lessen the slow searching and i know what it caused this - the calculation. so to prevent the slow motion while it calculates when a key is press, the search box will let you finish what your looking for then press SEARCH button.

how to do that?

Thanks.