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
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.
10 2525 NeoPa 32,556
Expert Mod 16PB
Start with a table of words you want found - [tblWord].[Word]. - SELECT DISTINCT
-
[Music].*
-
FROM [Music]
-
, [tblWord]
-
WHERE ([Music].[Title] Like '*' & [tblWord].[Word] '*')
NeoPa 32,556
Expert Mod 16PB
On a separate point, please check your PMs and let me have a reply when you can Phil.
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*'
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
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.
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 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.
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
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 32,556
Expert Mod 16PB
I suppose I should have guessed you knew better than to include it for no reason. I stand corrected :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |