I have several fields in a table that I want the user to be able to search for using wildcards. What I'm really looking for is a way to do a wildcard search that promotes a fair user experience (e.g. not clunky; decent speed; if not fast, then maybe it processes in the background). I know wildcard searches are frowned upon; and I may even scrap this feature if there's no viable solution. I would like to mention that I'm looking for ideas more than I am looking for specific code.
Here is an example to show what I have tried so far. Consider this hypothetical table with 3 fields that have text (the purpose of this table is irrelevant):
tableWidget
ID | Field1 | Field2 | Field3 |
1 | jimmy john | waldo johnson | carmen smith |
2 | francis | david smith | NULL |
3 | yvonne | harry t. | mr. waldo |
4 | dr. waldo, md | hal | NULL |
So I want to find all records that have "waldo" in it. My first hunch is to do something like this:
SELECT ID, Field1, Field2, Field3
FROM tableWidget
WHERE ID IN
(
SELECT ID FROM tableWidget WHERE
Field1 LIKE '*waldo*' OR
Field2 LIKE '*waldo*' OR
Field3 LIKE '*waldo*'
)
The problem here is that with the number of records I'm running, it is extremely slow. The second attempt I did was something like this:
SELECT ID, Field1, Field2, Field3
FROM tableWidget
WHERE ID IN
(
SELECT ID FROM tableWidget
WHERE Field1 & Field2 & Field3 LIKE '*waldo*'
)
This is still slow. The third way I tried this was to have a Form_Timer event that cycles through tableWidget and does a VBA comparison. My form's recordset was something like this:
SELECT * FORM tableWidget
WHERE ID IN (SELECT * FROM tempTable)
The snippet of code to append to the tape looked something like this"
With rsTableWidget
If Searching=True AND NOT .EOF Then
If INSTR(.Fields("Field1") & .Fields("Field2") & ".Fields("Field3"), "waldo") > 0 Then
rsTempTable.AddNew
rsTempTable.Fields("ID")=.Fields("ID")
rsTempTable.Update
mySubForm.Requery
End If
rsTableWidget.MoveNext
Else
Searching=False
rsTableWidget.Close: rsTempTable.Close
End If
End With
The good thing about this was that it would update in the background. The bad thing about this was that every iteration of Form_Timer, it would flicker. And when it found a record, the mySubForm.requery method would reset the form--not very user friendly.
Other methods I've considered was using VBA code to regularly parse through every word (delimited by spaces) and create a search table:
tableSearchTerm:
ID | searchTerm
1 | jimmy
1 | john
1 | waldo
1 | johnson
1 | carmen
1 | smith
2 | francis
2 | david
2 | smith
etc...
I haven't tried this yet, but I suppose the good thing would be that I could index the searchTerm. And I wouldn't have to use wildcards anymore because all the terms are associated with the ID. The bad thing is that this table will get huge quick!
Is there a better method?
