Solved Search all words in field against all words in another field

[Galaxiom]

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type)

Like?? Like won't find similarities in strings with transposed, extra or omitted characters. Damareau-Levenshtein specifically targets what typically happens in typos.

The database in your attachment looks interesting. Nice to have all those techniques combined in one search. I'll definitely be taking a good look at it. Always on the lookout for ways of finding similarities in big databases. Thanks.

Clearly the Levenshtein Distance is an important component of your search. Note that Damareau adds detection of transposition, omissions and insertions which are not covered by Levenshtein but is considerably more expensive to process. I think D-L covers similar ground to Simil too.

I eventually converted the D-L function to VB.NET and have it as a CLR function in SQL Server, which overcomes the performance limitations. Someone showed me a native TSQL version of it too. I compared the performance and found the SQL version was sixty percent slower than the CLR.

 

[CharlesDavenport]

i added tblExclusion, where a list of "blacklist" (excluded from search) is entered.
i intentionally left out the "." in the abbreviation (the new code also remove it from
the TextSearch).

Thankyou everyone for your responses! One day Ill get this VBA down!

Arnelgp, once again your code works perfectly for my requirements however, I have run into a slight problem.

I have used the module to undertake multiple searches ie multiple locations on one form with company names and entities in against the client list as shown below.

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE fncSearch([Client Name],Forms!ConflictsofInterestForm![Client Name]) Or fncSearch([Client Name],Forms!ConflictsofInterestForm![Group Companies]) Or fncSearch([Client Name],Forms!ConflictsofInterestForm![Subsidiary Companies]) Or fncSearch([Client Name],Forms!ConflictsofInterestForm![Possible 3rd Parties Affected]);

This works if the user has typed an entry into each

If one of these fields is null then everything crashes out and I get an error that it is too complicated as below.

I have tried adding in the VBA where the ISNull Statement is to return a value of " " or "0", I have also tried in the For Each v In var section an additional IF statement ie.
If IsNull(v) Then v=" "
Endif
and i have tried
If IsNull(v) Then v="0"
Endif

but this also throws up errors - any advice is appreciated.

 

[CharlesDavenport]

i added tblExclusion, where a list of "blacklist" (excluded from search) is entered.
i intentionally left out the "." in the abbreviation (the new code also remove it from
the TextSearch).

Works a treat thankyou!

And thankyou to everyone else's posts.....

Now I have run into a problem, I have used your search module in a number of instances in the sql query as detailed below, this works if all of the field have something in but, in many instances these will be blank ie null values and Access complains and stops macro (image below of error message)

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE Nz(fncSearch([Client Name],Forms!ConflictsofInterestForm![Client Name])," ") Or Nz(fncSearch([Client Name],Forms!ConflictsofInterestForm![Group Companies])," ") Or Nz(fncSearch([Client Name],Forms!ConflictsofInterestForm![Subsidiary Companies]), " ") Or Nz(fncSearch([Client Name],Forms!ConflictsofInterestForm![Possible 3rd Parties Affected]), " ");

In the VBA where you have the If IsNull(theValue) Then Exit Function I have tried to change this to If IsNull(theValue) Then theValue = " " (or "0") and this makes no difference to the result and I still get the expression error.

I have also tried to incorporate an If beneath the For Each v In var section of your VBA.
For Each v In var
If IsNull(v) Then v = " " (also tried v = "0")
End If
This is not liked by Access and throws up a compile error as it states the if body is not correct.

I have also tried to change the next step (the exclusion) as an elseif - which throws up a complie error as well.

Also tried the nz function around each of the SQL statements and this does not work either.

I apologize for the questions and appreciate the assistance, a simple database has grown legs above my knowledge and whist I have been able to figure out all the other VBA, criteria and macros this one is really stumping me!

 

[arnelgp]

replace your function with this one:

Public Function fncIsSubString(ByVal theValue As Variant, ByVal theText As String) As Boolean
    Dim var As Variant
    Dim v As Variant
    Dim tf As Boolean
    fncIsSubString = False
    If IsNull(theValue) Then Exit Function
    'trim the field value
    theValue = Trim$(theValue)
    If Len(theValue) = 0 Then Exit Function
    'make sure there is only 1 space between
    'words
    Do While InStr(theValue, "  ") > 0
        theValue = Trim$(Replace$(theValue, "  ", " "))
    Loop
    var = Split(theText)
    For Each v In var
        'skip this keyword if exists in tblExclusion
        '
        'note, i remove the "." from the word since in tblExclusion
        'i don't have them, eg "Co.", in table it is "Co"
        If DCount("1", "tblExclusion", "[Exclude] = '" & Replace$(Replace$(v, "'", "''"), ".", "") & "'") > 0 Then
            'do nothing
        Else
            tf = (theValue Like "*" & v & "*")
            If tf Then Exit For
        End If
    Next
    fncIsSubString = tf
End Function

regarding your query, you only need to Concatenate the Form's textboxes:

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE fncSearch([Client Name], Forms!ConflictsofInterestForm![Client Name] & " " & Forms!ConflictsofInterestForm![Group Companies] & " " & Forms!ConflictsofInterestForm![Subsidiary Companies] & " " & Forms!ConflictsofInterestForm![Possible 3rd Parties Affected])

 

[CharlesDavenport]

replace your function with this one:

Public Function fncIsSubString(ByVal theValue As Variant, ByVal theText As String) As Boolean
    Dim var As Variant
    Dim v As Variant
    Dim tf As Boolean
    fncIsSubString = False
    If IsNull(theValue) Then Exit Function
    'trim the field value
    theValue = Trim$(theValue)
    If Len(theValue) = 0 Then Exit Function
    'make sure there is only 1 space between
    'words
    Do While InStr(theValue, "  ") > 0
        theValue = Trim$(Replace$(theValue, "  ", " "))
    Loop
    var = Split(theText)
    For Each v In var
        'skip this keyword if exists in tblExclusion
        '
        'note, i remove the "." from the word since in tblExclusion
        'i don't have them, eg "Co.", in table it is "Co"
        If DCount("1", "tblExclusion", "[Exclude] = '" & Replace$(Replace$(v, "'", "''"), ".", "") & "'") > 0 Then
            'do nothing
        Else
            tf = (theValue Like "*" & v & "*")
            If tf Then Exit For
        End If
    Next
    fncIsSubString = tf
End Function

regarding your query, you only need to Concatenate the Form's textboxes:

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE fncSearch([Client Name], Forms!ConflictsofInterestForm![Client Name] & " " & Forms!ConflictsofInterestForm![Group Companies] & " " & Forms!ConflictsofInterestForm![Subsidiary Companies] & " " & Forms!ConflictsofInterestForm![Possible 3rd Parties Affected])

OK...

First of all I would like to appologise for being a pain and thankyou for the assistance with this.

arnelgp, your latest code and sql code has helped however, when there are blank (null) fields this is now (from what I can fathom) searching for blank spaces and returning all clients....

I have tried to amend your code so that it replaces with a 0 rather than a " " but this has not changed the end result.

Any thoughts from anyone?

 

[arnelgp]

try Nz() value that is most likely will not be written as company name:

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE
fncSearch([Client Name], Nz(Forms!ConflictsofInterestForm![Client Name],"@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Group Companies], "@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Subsidiary Companies],"@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Possible 3rd Parties Affected], "@!@!"))

 

[CharlesDavenport]

try Nz() value that is most likely will not be written as company name:

SELECT ClientList.[Job Reference], ClientList.[Client Name]
FROM ClientList
WHERE
fncSearch([Client Name], Nz(Forms!ConflictsofInterestForm![Client Name],"@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Group Companies], "@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Subsidiary Companies],"@!@!") & " " & Nz(Forms!ConflictsofInterestForm![Possible 3rd Parties Affected], "@!@!"))

Perfect!!!!! thankyou for all of your assistance on this it has been amazing!

 

[MajP]

I want the query to return both Someone Transport Limited and That Builder Ltd.
Following this i then later want to exclude "Limited", "Ltd", "Plc" etc so that all companies are returned.

This gave me an idea for a very flexible and powerful search interface.
Assume you think the name has Abbott, or Abot and maybe Raynor and maybe limited or Sons or LLC. You want to search all, some, a couple and you want to do this real quick.

So you type a list of words to search. Then you can pick some or all of them. And then you can decide if it is just like one or like all the words. Using the multiselect you can click and unclick to add to the search and it filters automatically You can easily add additional words.

This is something I will likely include in future dbs because it is so flexible and quick to adjust the search.