Robust Search Function in Access (1 Viewer)

lilanngel · Aug 17, 2012

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 · Aug 17, 2012

Welcome to the forum.

The sample posted here may be of interest to you.

lilanngel · Aug 17, 2012

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 · Aug 17, 2012

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 · Sep 27, 2012

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 · Oct 10, 2012

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 · Oct 10, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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 · Oct 11, 2012

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

gemma-the-husky · Oct 11, 2012

another way is to use a function

Code:

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 · Oct 12, 2012

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 · Apr 10, 2013

John Big Booty said:
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.

Robust Search Function in Access (1 Viewer)

New member

AWF VIP

New member

AWF VIP

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

Super Moderator

Registered User.

Registered User.

Attachments

Similar threads

Users who are viewing this thread