Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Creating Search Forms

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 12:41 pm    Post subject: Creating Search Forms Reply with quote

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.

Code:
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)

Code:
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.

Code:
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.
________
CANCER - UTERUS FORUMS
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group