Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching database with advanced search

Status
Not open for further replies.

bpitche

MIS
Mar 13, 2001
43
US
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
 
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
 

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
 
In a report. Brad Pitcher
The Hoover Company
bpitcher@hoover.com
 
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, " ")
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, " ")
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 [thumbsup2]. _________
Rott Paws

...It's not a bug. It's an undocumented feature.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top