Solved Ignore the order of words in a filter (1 Viewer)

[561414]

Hello everyone.

I have a form with textbox and a subform. The textbox filters the subform through this code:

Option Compare Database
Option Explicit

Private Sub txtSearch_Change()
    Dim rs As String
    
    rs = _
        "SELECT mm.MunicipioID, mm.Municipio " & _
        "FROM tbMexicoMunicipalities AS mm " & _
        "WHERE (((mm.Municipio) like '*" & Me.txtSearch.Text & "*'))" & _
        "ORDER BY mm.MunicipioID ASC;"
        
    Me.Subform_tbMexicoMunicipalities.Form.RecordSource = rs
End Sub

So let's say I have these records:
Valle Grande del Canyon
Bella Vista
Viento del Norte

If I want to reach "Bella Vista", and I type "vi" in my filter, it will return "Bella Vista" and "Viento del Norte", but I'd like to be able to keep writing like this "vi b" and make it return "Bella Vista", in fact, I'd like to make it return it even if I type something like "ist ell". Or if I want it to return "Valle Grande del Canyon", type something like "yon va gra". In a way, make the filter respect the order of letters in every word, but ignore the order of words. Do I split the text? and how do I use it in the sql query? how do you deal with this? does it make sense?

 

[cheekybuddha]

Just flying by ...

You can try something like:

Private Sub txtSearch_Change()
    Dim rs As String, terms as Variant, i As Integer
    
    terms = Split(Me.txtSearch.Text, " ")
    For i = 0 To UBound(terms)
      terms(i) = "mm.Municipio LIKE '*" & terms(i) & "*'"
    Next i
    rs = _
        "SELECT mm.MunicipioID, mm.Municipio " & _
        "FROM tbMexicoMunicipalities AS mm " & _
        "WHERE " & Join(terms, " AND ") & " " & _
        "ORDER BY mm.MunicipioID ASC;"
    Debug.Print rs
    Me.Subform_tbMexicoMunicipalities.Form.RecordSource = rs
End Sub

(NB untested)

hth,

d

 

[Galaxiom]

Avoid using custom functions and instead use the power of the engine to test all occurrences at once.

Use a form to pop the search terms into a table. Create a query with a Cartesian join (a join with no condition that creates a record for every combination of the records in both tables.

WHERE Data.SearchedField LIKE "*" & SearchTerms.Term &"*"

This lets you have a many search terms as you like, and is an incredibly simple query with as little as one Where expression that makes multiple tests at once returning a list of records with any of the terms included.

Different use of the wild card can change the search to Starts With or Ends With logic on the basis of words anywhere in the field or reversing the logic and using NOT LIKE.

The results can be narrowed or widened by using AND and OR in the query.

 

[arnelgp]

check and test

 

[MajP]

This is kind of a version of that and may be of interest. The user wanted to search one, some, or all of a group of words. You type your search words and pick which ones to search if you want to narrow or expand the search. Not exactly what you want.

 

[561414]

cheekybuddha:​

Yess, that totally does the search ignoring the order of words, I imagined it would be a bigger piece of code, but it's just a few changes from the original. It produces a little error in the Where clause when the textbox is empty, but that's easy to handle. Thank you very much. Works great!

Galaxiom:​

I think I'm still a little incompetent to implement your suggestion. If I understood right, let's say I have tables tblTerms and tblMunicipalities, then I use vba to populate tblTerms with whatever is in the textbox at the time of each keystroke and then using a castersian join query for the recordsource of the form, I'll get the results... doesn't it have to populate and empty the table after each keystroke? I would also like to know why I should avoid vba for such a thing, is it faster this way? Thank you!

arnelgp:​

arnelgp, I tested your example and it's great! It does the search just the way I need. I see that you also implemented a a clean up before the search, is that right?

    If Len(sText) <> 0 Then
        Do While InStr(1, sText, "  ") <> 0
            sText = Replace$(sText, "  ", " ")
        Loop
        sText = Trim$(sText)

It's very interesting, I had not accounted for that kind of problem. Great stuff for a learner like me as usual!
Thank you, again!

MajP:​

Hey there, MajP, I checked it out and I think I'm doing something wrong. It seems like I can only send the search term to the right panel when I hit the tab key, but only one word at a time. It also throws an error somewhere. It's not exactly what I need, but I could give it a use when I'm selecting a product from my database and I want to send multiple emails to different providers of the product and get a quote from them. So thank you for the idea!

Wow, so many replies. Thank you all and sorry for not replying before.

 

[cheekybuddha]

Glad you got a solution. As you noted, my suggestion needs a small tweak to make it handle an empty textbox. Something similar to:

' ...
rs = _
        "SELECT mm.MunicipioID, mm.Municipio " & _
        "FROM tbMexicoMunicipalities AS mm " & _
        IIf(UBound(terms) >= 0, "WHERE " & Join(terms, " AND ") & " ", vbNullString) & _
        "ORDER BY mm.MunicipioID ASC;"
' ...

Keep on learning!

 

[arnelgp]

had not accounted for that kind of problem

the EU might have some mistakes on adding double (or even tripple) spaces between words, so the code
just replace them with a single one.