473,322 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 2525
NeoPa
32,556 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,556 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,556 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,556 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

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

Similar topics

6
by: Keith | last post by:
I have a small form which searches a SQL DB on several fields - ie. WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx How can I do this so that if one search criteria is left blank, it...
3
by: Trevor | last post by:
I am looking to write a complex search facility for customers to search on product descriptions. This area is something like data mining, and crosses with a number of db areas. I would like any...
4
by: Axel | last post by:
Is it possible to write a Stored Procedure that takes a string of search keywords as argument and returns the recordset? At the mo I am passing the WHERE String as argument. I got this...
1
by: Brian | last post by:
Hello I am trying to implement 'Search' (Basic Keyword search) functonality on a site. Home page has a search text box and a 'GO' button. When users will type in a keyword and presses 'GO', URL...
1
by: ajdude | last post by:
All I need, is to have some preset words, and when the page loads, if the preset words that are in the code are on the page, and alert come like like "Word found" or if one of the preset words like...
1
by: HYRY | last post by:
I want to add some simple search function for my homepage. It need to search through all the html files of my homepage (about 300 pages), and highlight the search words. I made some test with...
35
by: shapper | last post by:
Hello, I am creating a search engine which searches keywords inside SQL database and displays the results in a web page. In the results web page I would like to give a grey background to the...
13
by: Robertf987 | last post by:
Hi, Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000. What I want is to be able to do a...
4
by: kkshansid | last post by:
i have a database as table name school field name location eg in location column data green school,tagore garden,chink road,jammu i want to make search on location such that when user enter ...
1
by: 21thermostat | last post by:
I am currently working on a store search function. The search criteria are quite complex, for example: Footage >= 50 AND (SizeCode == SizeType.Large OR MobileEnd == "ABC") I think that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.