"Fuzzy" search, like a search engine

[dustinros]

Hello!

I have a free text box that searches one field using this code:
_______________
SQL = "Select [Desc] from [tblParts]" & _
" where [Desc] LIKE ""*" & Me.txtSearch.Text & "*"""
_______________

The only problem, is that it if I have a record that says "Connector, 300w" and I type in "300w connector" I wont get a result.

If anyone has any ideas as to how to make the search function less focused, more like a search engine, I'd really appreciate it... I'm stuck.

Thanks everyone!

 

[PHV]

In the AfterUpdate event procedure of txtSearch:
Dim a, s, SQL
SQL = ""
a = Split(Me!txtSearch)
For Each s In a
If Trim(s) <> "" Then
SQL = SQL & " AND [Desc] Like '*" & s & "*'"
End If
Next
If SQL <> "" Then
SQL = "SELECT [Desc] FROM tblParts WHERE" & Mid(SQL, 5)
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GingerR]

you could separate out your input by the space(s), using MID, LEFT, RIGHT, INSTR functions, and create a list of things you're searching for by building a WHERE statement comprised of the number of words:

where [Desc] like "*300w*" and [Desc] like "*connector*"

You'd do that with vba code so the WHERE string is as long as the number of words someone types in.


Another option would of course be to categorize all of your stuff, so users pick from items like

Connector
Resistor
Widget

then put in pertinent info for each one. Do you have your tables set up so that there's a field for ItemType (Connector, etc) and Power (300W) or other discrete bits of information?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dustinros]

Hi PHV, great - thank you! I have one question, and forgive me b/c I am not that savvy with Access, where is the AfterUpdate Event Procedure? Heres is the code:
_____________________________________________________
Option Compare Database
Option Explicit

Private Sub txtSearch_Change()
Dim SQL As String

Me.txtDefinition = Null

Me.txtManuNAM = Null

Me.txtManuNUMS = Null

SQL = "Select [Desc] from [tblParts]" & _
" where [Desc] LIKE ""*" & Me.txtSearch.Text & "*"""

Me.lstTerms.RowSource = SQL


End Sub

Private Sub lstTerms_DblClick(Cancel As Integer)
Dim SQL As String

SQL = "Select [ROS_Part#] from [tblParts]" & _
" where [ROS_Part#]= """ & Me.lstTerms & """"

Me.txtDefinition = DLookup("[ROS_Part#]", "tblParts", _
"[Desc]=""" & Me.lstTerms & """")

Me.txtDefinition.SetFocus

SQL = "Select [Mftr_NAM] from [tblParts]" & _
" where [Mftr_NAM]= """ & Me.lstTerms & """"

Me.txtManuNAM = DLookup("[Mftr_NAM]", "tblParts", _
"[Desc]=""" & Me.lstTerms & """")

Me.txtManuNAM.SetFocus

SQL = "Select [Mftr_NUM] from [tblParts]" & _
" where [Mftr_NUM]= """ & Me.lstTerms & """"

Me.txtManuNUMS = DLookup("[Mftr_NUM]", "tblParts", _
"[Desc]=""" & Me.lstTerms & """")

Me.txtManuNUMS.SetFocus


End Sub
____________________________________________
Thanks so much!! This forum is always a lifesaver...

-D

 

[dustinros]

Hi Ginger --

I'm only searching one field, a description, and there is no uniform entry in those fields, and lots of different information in there, which is what's making this difficult. Thanks though!!

 

[GingerR]

PHV put in code exactly what I suggested!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

You have code in the Change event procedure of txtSearch.
I don't think it's a good idea as this event is fired on each keystroke in the Textbox ...
My suggestion was to get rid of this event and to play with the AfterUpdate event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886