Searching database with advanced search

[bpitche]

I would like to know if anyone could help me write some code so someone can enter words or a phrase in a textbox, then select whether they want to search for the exact phrase, any words or all words. I know how to search for the exact phrase, but I don't know how to do it the other two ways. Any ideas would be helpful. Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[jdegeorge]

I find myself referring to a great website called The Access Web. Here's a link for a possible solution for you

http://www.mvps.org/access/forms/frm0045.htm

Hope this helps.

Jim "Get it right the first time, that's the main thing..."

 

[bpitche]

This is not really what I am looking for. I want it to involve just one text box and 3 radio options. If anyone has any other idea, I would appreciate it. I want it to be similar to what internet search engines do.

Thanks for the suggestion. Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[thornmastr]

Once you find the information you are looking for, how do you expect to display it. In a form, a document, a web page, orwhat?
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[bpitche]

In a report. Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[RottPaws]

I've actually thought about this recently. I want to search through a table with a memo field that has order comments. I think I've got it worked out *HOW* to do it, . but haven't gotten around to doing it yet.

My idea is to set up three option buttons: Exact Phrase, All Words, Any words.

I would have a "Search" command button. In the Click event of that button, do a Select Case on the option group to determine which option has been selected.

Create string variables called strWHERE, strWord, and strConjunction and integers called iSpace and iStart

The first one is easy . . .
Case 1 'Exact Phrase
strWHERE = "WHERE tablename.comment = *" & txtInput & "* "

For the 2nd and third, look for a space in txtInput using the InStr() function. Assign the first space to the iSpace variable. Assign the first word to a variable and add that to the WHERE clause of your query.

iStart = InStr(1, txtInput, " &quot
strWord = LEFT(txtInput, iStart - 1)
strWHERE = "WHERE tablename.comment = *" & strWord & "* "
strConjunction = " And " ' if option 2
or
strConjunction = " Or " ' if option 3

iStart = iStart + 1

Now loop throught the rest of the string looking for more spaces until you don't find anymore. Assign each word to the strWord variable and add it to the WHERE clause

Do Until iSpace = 0
iSpace = InStr(iStart, txtInput, " &quot
If iSpace > 0 And Not Isnull(iSpace) Then
strWord = Mid(txtInput, iStart, iSpace - iStart)
iStart = iSpace + 1
Else
strWord = Right(txtInput, Len(txtInput) - (iStart - 1))
End If

strWHERE = strWHERE & strConjunction & "*" & strWord & "* "

Loop

When there are no more spaces, and the last word has been added to the WHERE clause, then set the recordset of the form or report

Open the form or report hidden
DoCmd.OpenForm "FormName", , , , , acHidden

Set the recordset
Forms!FormName.RecordSource = "Select * From Table " & _
strWHERE & _
"ORDER BY field3, field5;"

Show the form/report
Forms!FormName.Visible = True

--------------------------------------

I got a little carried away, but that's my idea for something that should work . _________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[bpitche]

That is exactly what I am interested in. I have not tried it yet, but I will let you know how it works when I do.

Thank you very much!! Brad Pitcher
The Hoover Company
bpitcher@hoover.com