Complex Search for words in descriptions

[PhilOfWalton]

I belong to a music club and we have a database of all the music.
The relevant table is Music, with MusicID as the primary key and Title (other fields are irrelevant).
We have to put together a themed music program, and next months theme is royalty. So I want to search for words like "Emperor", "Royal", "King", "Elizabethan", "Tsar". anywhere in the title. This needs to be done in one pass rather than searching for each word separat
My mind is a comparative blank on where to start. My thoughts are to create a temporary table using all the words in all the titles (no duplicates) and use that a s source for a very very long multi-select list box.
Then where?

Any thoughts gratefully received.

Phil

[NeoPa]

Start with a table of words you want found - [tblWord].[Word].

SELECT DISTINCT
         [Music].*
FROM     [Music]
       , [tblWord]
WHERE    ([Music].[Title] Like '*' & [tblWord].[Word] '*')

[NeoPa]

On a separate point, please check your PMs and let me have a reply when you can Phil.

[jforbes]

I'm curious why you think you need to use a table other than the Music Table?

You could create a continuous Form based on the Music Table. Then drop on a textbox where the user types in a space separated list of keywords. Then either on a button press or losing focus on the Keyword textbox, you could split the keywords and build a Where Clause and apply it to the Forms Filter.

Basically the user would enter:

Emperor Royal King Elizabethan Tsar 

and your code would create this:

Title LIKE '*Emperor*' OR Title LIKE '*Royal*' OR Title LIKE '*King*' OR Title LIKE '*Elizabethan*' OR Title LIKE '*Tsar*' 

[PhilOfWalton]

You're absolutely right, I don't need that table.
At the moment, I have a routine which filters as you type each letter

Private Sub TxtFilter_Change()
 
    'Title like "*roy*"OR Title Like "*bl*"
 
    Dim Fltr As String
 
    ' If we return no records, tempotarily remove the filter
    If Me.RecordsetClone.RecordCount = 0 Then
        Me.FilterOn = False
    End If
 
    TxtFilter.SetFocus
 
    If Nz(Me.TxtFilter.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
        ' If a partial value is typed, filter for a partial company name match.
    Else
        If Right(TxtFilter.Text, 1) <> "~" Then               ' Last letter typed not a tilde
           ' FilterStg = filterst & Right(TxtFilter, 1)
            Fltr = Fltr & "Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
        End If
 
        Me.Form.Filter = Fltr
        Me.FilterOn = True
    End If
 
    Me.TxtFilter.SetFocus
 
    ' Move the cursor to the end of the combo box.
    If Me.RecordsetClone.RecordCount = 0 Then
        Exit Sub
    End If
 
    Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
 
End Sub

This works perfectly.
What I want to do now is having typed in the first few letters of the first word I am looking for, type a tilde (~) to start adding a further word. This has got to create an additional
OR "Title Like '*" & Next Word, "'", "''") & "*'"

Haven't really had time to have a go at this, but it seems tricky.

Phil

[jforbes]

This looked fun, I came up with this. I think it's close to what you are looking for:

Private Sub txtKeywords_Change()
 
    Dim sKeywords As String
    Dim sKeyword As Variant
    Dim lCurrentSelStart As Long
    Dim sSQL As String
 
    lCurrentSelStart = Me.txtKeywords.SelStart
    sKeywords = Nz(Me.txtKeywords.Text, "")
 
    ' Get Filter
    For Each sKeyword In Split(sKeywords, " ")
        If Len(Trim(sKeyword)) > 0 Then sSQL = sSQL & "Title LIKE '*" & sKeyword & "*' OR "
    Next sKeyword
    If Len(sSQL) > 0 Then sSQL = Left(sSQL, Len(sSQL) - 4)
 
    ' Apply Filter
    If Len(sSQL) > 0 Then
        Me.Filter = sSQL
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
 
    ' reset txtKeywords back to where it was
    Me.txtKeywords.SetFocus
    Me.txtKeywords.Value = sKeywords
    Me.txtKeywords.SelStart = lCurrentSelStart
 
End Sub

In the above, the keywords are split on a space, which you could replace the space in the For Each with a ~ (Tilde) and it would do what you are talking about. Personally, I like the space to split as it's more intuitive, but using a ~ would be more powerful.

[PhilOfWalton]

Thanks for that, that should work, but that is working on complete words. The code that I outlined is rather fun because, as you type each letter in the TxtFilter search box, a finer & finer filter is applied.
So with the TxtFilter blank there are 2639 record.
On typing "r", not surprisingly it drops to 2130 records (Lots of music titles have an "r" in them. "ro" pulls it down to 266 and "roy" down to 2 copies of Handel's Music for the Royal Fireworks and Berlioz Royal Hunt & Storm (Trojans).
What I expect to happen is that I then enter a tilde in the TxtFilter and all 2639 records show. as I then type in a "k", the three records already mentions + any titles with a "k" in the title should show. By the time I have typed in "rot~ki" I would expect to see only records with "royal" and "king" it the title.

Phil

[NeoPa]

I suspect you may have misunderstood the post a bit there Phil.

As JForbes explains below the code, his approach uses a space rather than a tilde, but that's easily changed by changing line #12 to use "~" instead of " ". Otherwise it seems to do just what you've specified.

I would suggest that much of the code is unnecessary. The .Value needn't be changed/set - which is never a good idea within a Control_Change() procedure anyway. Essentially, the code shouldn't change anything about the control just by setting the form's filter, so the code to set it back is not required.

I'm afraid your requirement to handle this within a form and that the words be entered on the form, wasn't clear originally so my suggestion wasn't based on that. However, JForbes' code seems to do the job pretty well I suspect.

[PhilOfWalton]

Thanks for all your help. My original idea uf searching for words from a combo boc showing a list of all the words in the titles was useless as there were over 4000 words.

Problem is now solved and in addition there is an extra filter for Media Type (Disc, Vinyl, Tape 78s - I go back a long way).
So on changing the TxtFilter or Updating the SelectMediaType Combo I run

Sub FilterForm()
 
'MediaTypeID = 1 AND (Title Like '*roy*' OR Title Like '*kin*')
 
    Dim Fltr As String
 
    If SelectMediaType > 0 Then                         ' Media Type not all
        Fltr = "MediaTypeID = " & SelectMediaType
    End If
 
    ' If we return no records, tempotarily remove the filter
    If Me.RecordsetClone.RecordCount = 0 Then
        Me.FilterOn = False
    End If
 
    TxtFilter.SetFocus
 
    If Left(TxtFilter.Text, 1) = "~" Then                ' Don't allow a tilde as first letter
        TxtFilter.Text = ""
    End If
 
    If Fltr > "" Then                               ' Media Type selected
        Fltr = Fltr & " AND "
    End If
    Fltr = Fltr & "(Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
    Fltr = Replace(Fltr, "~", "*' OR Title Like '*")
    Fltr = Fltr & ")"
    Me.Form.Filter = Fltr
    Me.FilterOn = True
 
 
    Me.TxtFilter.SetFocus
 
    ' Move the cursor to the end of the input text box.
    If Me.RecordsetClone.RecordCount = 0 Then
        Exit Sub
    End If
 
    Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
 
End Sub
 

Works perfectly and surprisingly fast.

Phil

[jforbes]

Glad you got it working Phil. Keep posting these fun challenges. =)

NeoPa,
I didn't expect it, but changing the Filter of the Form while inside the OnChange Event causes the search TextBox to fire off it's Before and AfterUpdate events and the entirety of the text of the TextBox becomes selected. I'm not sure, but I think the Form is firing a Refresh. So that last bit of code to SetFocus and update the selected text seems necessary. I didn't like it, but Access would act all crazy without it.

[NeoPa]

I suppose I should have guessed you knew better than to include it for no reason. I stand corrected :-)