Creating Search Forms
Although Access contains
some great find functions it really doesn't cater for searching memo fields and
returning scored hits. The following example shows you how to return a result
for a search for text give the number of word hits. It relies on very little
code and a few queries (All of which should be reasonably quick). The results
return a count of the number of hits.
Create a new form not based on any recordset. Put a text box on it and
call it SearchText. Create a button on the form and call it Search. Create 8 textboxes
called word1 to word8. Bring up the code window and paste the following in.
Private Sub Search_Click()
On Error GoTo Err_search_Click
Dim Spacepos As
Long 'Position
of next space in search string
Dim Lengthstr As
Long 'Length of
search string
Dim Texttemp As String
'Search String'
Dim x as integer
Texttemp = SearchText.Value 'set texttemp variable
to value of entire text box
If Texttemp ="" Then Exit Sub 'If no text exit
Texttemp = Texttemp & Space(8)'Add 8 spaces to ensure string does not
stop short
Lengthstr = Len(Texttemp)
'Get length of string for
for x = 1 to 8
Spacepos = InStr(1,Texttemp, " ",
1) 'Get next space
Select Case x
Case 1
Word1.Value = (Left(Texttemp, (Spacepos - 1)))
Case 2
Word2.Value = (Left(Texttemp, (Spacepos - 1)))
Case 3
Word3.Value = (Left(Texttemp, (Spacepos - 1)))
Case 4
Word4.Value = (Left(Texttemp, (Spacepos - 1)))
Case 5
Word5.Value = (Left(Texttemp, (Spacepos - 1)))
Case 6
Word6.Value = (Left(Texttemp, (Spacepos - 1)))
Case 7
Word7.Value = (Left(Texttemp, (Spacepos - 1)))
Case 8
Word8.Value = (Left(Texttemp, (Spacepos - 1)))
End select
Texttemp = Right(Texttemp, (Lengthstr - Spacepos)) 'Delete that word and
space
Lengthstr = Len(Texttemp) 'Get length of string
Next
Docmd.Openquery("qry_SearchResults")
Exit_search_Click:
Exit Sub
Err_search_Click:
MsgBox Err.Description
Resume Exit_search_Click
End Sub |
Save the form as frm_Search and close the form. Create a
new query drag in the table you are searching and drag in only the unique id for
the record and the field you want to search for text. Click off the show
checkbox for the field you are searching for text (This query will now just
return the unique id). In the criteria for
the field you are searching paste the following (This is the compare statement)
|
Like
("*" & [Forms]![frm_Search]![Word1] & "*") Or Like
([Forms]![frm_Search]![Word1] & "*") Or Like ("*" &
[Forms]![frm_Search]![Word1]) Or Like [Forms]![frm_Search]![Word1] |
Save the query as qry_SearchWord1 close the query
and copy it 7 times creating qry_SearchWord1 through to qry_SearchWord8. In each of the queries replace Word1
in the line above with Word2 through to Word8 respectively for each query.
Create another query. Make it a union query by selecting Query ->
SQL Specific -> Union Then paste the following SQL into the resulting window.
SELECT qry_SearchWord1.*
FROM qry_SearchWord1
UNION ALL SELECT qry_SearchWord2.*
FROM qry_SearchWord2
UNION ALL SELECT qry_SearchWord3.*
FROM qry_SearchWord3
UNION ALL SELECT qry_SearchWord4.*
FROM qry_SearchWord4
UNION ALL SELECT qry_SearchWord5.*
FROM qry_SearchWord5
UNION ALL SELECT qry_SearchWord6.*
FROM qry_SearchWord6
UNION ALL SELECT qry_SearchWord7.*
FROM qry_SearchWord7
UNION ALL SELECT qry_SearchWord8.*
FROM qry_SearchWord8 |
Save the query as qry_SearchUnion. Close it create a new
query. Bring in the qry_SearchUnion query. Drag the uniqueid field into the
fields twice. Choose from the menu view Totals. Under one of the fields total
should be Group By and the other should be Count. Save the query as
qry_SearchResults and close it.
Run the search form. You should be able to type in the
words you want to search for seperated by spaces, and hit the search button. It
should open the query results for you.
| 