Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Bournemouth, Dorset, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search in keyword table (Access 97)

    Hello, I'm working on an existing database, I have a unit table and a course table and a link table unit_course which links a unit to a course or courses. In the unit table there is a memo field which contains keywords for that unit. What would be the best way for a novice to set up a form which searched for keywords in this field and then listed all of the units which contained say three of the keywords, two of the keywords or one of the key words. At the moment the key words are just typed into the field (should they be comma delimited or anything?). I know this is a bit much to ask but thank you very much for any suggestions/advice (at present the database has no forms/frontend so this will be the first of many forms).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: search in keyword table (Access 97)

    Creating a full-fledged keyword search engine is not a trivial task. but you can create a simple one without much work. Here is a very simple example:

    Create a single or continuous form based on the unit table. Add the following to the form header or footer section:
    - A text box named txtKeywords.
    - An option group with two radio buttons labeled "And" and "Or", with option values 1 and 2, respectively. Name it grpFilterOptions. (The easiest way to create an option group is through the control wizard.)
    - A command button cmdFilter, with caption "Filter" or something like that. Create an On Click event procedure for the command button:

    Private Sub cmdFilter_Click()
    Dim strFilter As String
    Dim strOpt As String
    Dim intPos1 As Integer
    Dim strSearch As String
    Dim strWord As String
    On Error GoTo ErrHandler
    If IsNull(Me.txtKeywords) Then
    Me.Filter = ""
    Me.FilterOn = False
    Exit Sub
    End If
    strSearch = Me.txtKeywords
    If Me.grpFilterOptions = 1 Then
    strOpt = " And "
    Else
    strOpt = " Or "
    End If
    Do
    intPos1 = InStr(strSearch, " ")
    If intPos1 = 0 Then
    strWord = strSearch
    Else
    strWord = Left(strSearch, intPos1 - 1)
    strSearch = Mid(strSearch, intPos1 + 1)
    End If
    strFilter = strFilter & strOpt & "[Notes] Like " & Chr(34) & "*" & strWord & "*" & Chr(34)
    Loop Until intPos1 = 0
    strFilter = Mid(strFilter, Len(strOpt) + 1)
    Me.Filter = strFilter
    Me.FilterOn = True
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Replace Notes by the name of the memo field.

    Users can enter keywords, separated by a space, select a filter option and click the command button to filter the records displayed in the form.

    Note: if you want to add the option of filtering on whole words only, you'd have to write more complicated code. The amin problem is to determine what constitutes a whole word.

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Bournemouth, Dorset, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search in keyword table (Access 97)

    Thank you very much for this - I will try it as soon as I get some data for the keyword section

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search in keyword table (Access 97)

    I tried using your code for a database I keep containing Access instructions and code I save from the lounge. There is a memo field containing almost 500 records. It was becoming hard to find the entry I knew was there. With the form screen I created based on your instructions, I am thrilled with the result because I can now filter on just the entry or entries I want to look at.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •