469,125 Members | 1,671 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Complex Search for words in descriptions

PhilOfWalton
1,430 Expert 1GB
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
Apr 20 '16 #1

✓ answered by jforbes

This looked fun, I came up with this. I think it's close to what you are looking for:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtKeywords_Change()
  2.  
  3.     Dim sKeywords As String
  4.     Dim sKeyword As Variant
  5.     Dim lCurrentSelStart As Long
  6.     Dim sSQL As String
  7.  
  8.     lCurrentSelStart = Me.txtKeywords.SelStart
  9.     sKeywords = Nz(Me.txtKeywords.Text, "")
  10.  
  11.     ' Get Filter
  12.     For Each sKeyword In Split(sKeywords, " ")
  13.         If Len(Trim(sKeyword)) > 0 Then sSQL = sSQL & "Title LIKE '*" & sKeyword & "*' OR "
  14.     Next sKeyword
  15.     If Len(sSQL) > 0 Then sSQL = Left(sSQL, Len(sSQL) - 4)
  16.  
  17.     ' Apply Filter
  18.     If Len(sSQL) > 0 Then
  19.         Me.Filter = sSQL
  20.         Me.FilterOn = True
  21.     Else
  22.         Me.FilterOn = False
  23.     End If
  24.  
  25.     ' reset txtKeywords back to where it was
  26.     Me.txtKeywords.SetFocus
  27.     Me.txtKeywords.Value = sKeywords
  28.     Me.txtKeywords.SelStart = lCurrentSelStart
  29.  
  30. 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.

10 2269
NeoPa
32,161 Expert Mod 16PB
Start with a table of words you want found - [tblWord].[Word].
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.          [Music].*
  3. FROM     [Music]
  4.        , [tblWord]
  5. WHERE    ([Music].[Title] Like '*' & [tblWord].[Word] '*')
Apr 20 '16 #2
NeoPa
32,161 Expert Mod 16PB
On a separate point, please check your PMs and let me have a reply when you can Phil.
Apr 20 '16 #3
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Emperor Royal King Elizabethan Tsar 
and your code would create this:
Expand|Select|Wrap|Line Numbers
  1. Title LIKE '*Emperor*' OR Title LIKE '*Royal*' OR Title LIKE '*King*' OR Title LIKE '*Elizabethan*' OR Title LIKE '*Tsar*' 
Apr 21 '16 #4
PhilOfWalton
1,430 Expert 1GB
You're absolutely right, I don't need that table.
At the moment, I have a routine which filters as you type each letter
Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtFilter_Change()
  2.  
  3.     'Title like "*roy*"OR Title Like "*bl*"
  4.  
  5.     Dim Fltr As String
  6.  
  7.     ' If we return no records, tempotarily remove the filter
  8.     If Me.RecordsetClone.RecordCount = 0 Then
  9.         Me.FilterOn = False
  10.     End If
  11.  
  12.     TxtFilter.SetFocus
  13.  
  14.     If Nz(Me.TxtFilter.Text) = "" Then
  15.         Me.Form.Filter = ""
  16.         Me.FilterOn = False
  17.         ' If a partial value is typed, filter for a partial company name match.
  18.     Else
  19.         If Right(TxtFilter.Text, 1) <> "~" Then               ' Last letter typed not a tilde
  20.            ' FilterStg = filterst & Right(TxtFilter, 1)
  21.             Fltr = Fltr & "Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
  22.         End If
  23.  
  24.         Me.Form.Filter = Fltr
  25.         Me.FilterOn = True
  26.     End If
  27.  
  28.     Me.TxtFilter.SetFocus
  29.  
  30.     ' Move the cursor to the end of the combo box.
  31.     If Me.RecordsetClone.RecordCount = 0 Then
  32.         Exit Sub
  33.     End If
  34.  
  35.     Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
  36.  
  37. 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
Apr 21 '16 #5
jforbes
1,107 Expert 1GB
This looked fun, I came up with this. I think it's close to what you are looking for:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtKeywords_Change()
  2.  
  3.     Dim sKeywords As String
  4.     Dim sKeyword As Variant
  5.     Dim lCurrentSelStart As Long
  6.     Dim sSQL As String
  7.  
  8.     lCurrentSelStart = Me.txtKeywords.SelStart
  9.     sKeywords = Nz(Me.txtKeywords.Text, "")
  10.  
  11.     ' Get Filter
  12.     For Each sKeyword In Split(sKeywords, " ")
  13.         If Len(Trim(sKeyword)) > 0 Then sSQL = sSQL & "Title LIKE '*" & sKeyword & "*' OR "
  14.     Next sKeyword
  15.     If Len(sSQL) > 0 Then sSQL = Left(sSQL, Len(sSQL) - 4)
  16.  
  17.     ' Apply Filter
  18.     If Len(sSQL) > 0 Then
  19.         Me.Filter = sSQL
  20.         Me.FilterOn = True
  21.     Else
  22.         Me.FilterOn = False
  23.     End If
  24.  
  25.     ' reset txtKeywords back to where it was
  26.     Me.txtKeywords.SetFocus
  27.     Me.txtKeywords.Value = sKeywords
  28.     Me.txtKeywords.SelStart = lCurrentSelStart
  29.  
  30. 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.
Apr 21 '16 #6
PhilOfWalton
1,430 Expert 1GB
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
Apr 21 '16 #7
NeoPa
32,161 Expert Mod 16PB
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.
Apr 21 '16 #8
PhilOfWalton
1,430 Expert 1GB
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

Expand|Select|Wrap|Line Numbers
  1. Sub FilterForm()
  2.  
  3. 'MediaTypeID = 1 AND (Title Like '*roy*' OR Title Like '*kin*')
  4.  
  5.     Dim Fltr As String
  6.  
  7.     If SelectMediaType > 0 Then                         ' Media Type not all
  8.         Fltr = "MediaTypeID = " & SelectMediaType
  9.     End If
  10.  
  11.     ' If we return no records, tempotarily remove the filter
  12.     If Me.RecordsetClone.RecordCount = 0 Then
  13.         Me.FilterOn = False
  14.     End If
  15.  
  16.     TxtFilter.SetFocus
  17.  
  18.     If Left(TxtFilter.Text, 1) = "~" Then                ' Don't allow a tilde as first letter
  19.         TxtFilter.Text = ""
  20.     End If
  21.  
  22.     If Fltr > "" Then                               ' Media Type selected
  23.         Fltr = Fltr & " AND "
  24.     End If
  25.     Fltr = Fltr & "(Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
  26.     Fltr = Replace(Fltr, "~", "*' OR Title Like '*")
  27.     Fltr = Fltr & ")"
  28.     Me.Form.Filter = Fltr
  29.     Me.FilterOn = True
  30.  
  31.  
  32.     Me.TxtFilter.SetFocus
  33.  
  34.     ' Move the cursor to the end of the input text box.
  35.     If Me.RecordsetClone.RecordCount = 0 Then
  36.         Exit Sub
  37.     End If
  38.  
  39.     Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
  40.  
  41. End Sub
  42.  
Works perfectly and surprisingly fast.

Phil
Apr 22 '16 #9
jforbes
1,107 Expert 1GB
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.
Apr 22 '16 #10
NeoPa
32,161 Expert Mod 16PB
I suppose I should have guessed you knew better than to include it for no reason. I stand corrected :-)
Apr 22 '16 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Trevor | last post: by
4 posts views Thread by Axel | last post: by
1 post views Thread by Brian | last post: by
1 post views Thread by HYRY | last post: by
35 posts views Thread by shapper | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.