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

[CharlesDavenport]

Hi all

New to access and muddling my way through.

I need to create a search which will search each word of a string on a form against each word in a string on another field in a different table.

I can get it to work using a single word in the search field on a form using the below criteria:

like '*'& [Forms]![Client]![Client Name] &'*'

Which returns the right results but when I have say:

Someone Transport and I want it to match either Someone or Transport it returns nothing on the query....

Any help is appreciated.

 

[CJ_London]

provide some realistic example data -do you want to match whole words or part words - so would 'target' be matched to 'targets' for example? Or from your very simple example perhaps 'Ltd' to 'Limited', 'Co' to 'Company'

you will almost certainly need to use a vba function.

 

[CharlesDavenport]

I have a table with list of company names

eg
Someone Transport Limited
That Builder Ltd
Random Painter

and I have a form which an emplyee fills in and undertakes a search on the company name they have filled in against the table of company names.

eg Search Field: Someone Builders

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.

Basically this is to undertake a new client check against a current client list.

 

[arnelgp]

create a function and call it in your query:

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
    If IsNull(theValue) Then Exit Function
    var = Split(theText)
    For Each v In var
        tf = (theValue Like "*" & v & "*")
        If tf Then Exit For
    Next
    fncIsSubString = tf
End Function

your query will look like:

SELECT * FROM yourClientTable WHERE fncIsSubString([ClientNameField], [Forms]![Client]![Client Name])

 

[arnelgp]

need sample:

 

[CharlesDavenport]

Thankyou Soooo Much!

Your sample works perfectly now I need to put it into my database..

Also need to add exclusion words as well into the SQL...

 

[CharlesDavenport]

need sample:

OK struggling here again!

realized I cant add my exclusion words in the SQL as they exclude the whole result not just that word and trying to now edit your code (which works perfectly)....

Trying to remove the exclusion words once the string has been split and then it gets fed into query.

everything ive tried so far hasn't worked!

I tried creating "voidwords = "Limited" and then searching for those void words and replacing with "" but no luck any guidance is appreciated.

 

[theDBguy]

Also need to add exclusion words as well into the SQL...

Hi. What do you mean by that? Are you saying you want the user to enter some words to exclude some records from the query result? As in, if the record contains those words, then it should not be included in the result?

 

[CharlesDavenport]

Hi. What do you mean by that? Are you saying you want the user to enter some words to exclude some records from the query result? As in, if the record contains those words, then it should not be included in the result?

Than for the reply.... So the user will enter a company name such as "Someones Company Limited" but because i want it to search for someones and company and return any results with those two words in it also searches for limited which then returns lots of unwanted results.

The code written by arnelgp works perfectly in respect that it splits the string into its separate words but I do not want the word limited to be searched...

 

[CharlesDavenport]

Than for the reply.... So the user will enter a company name such as "Someones Company Limited" but because i want it to search for someones and company and return any results with those two words in it also searches for limited which then returns lots of unwanted results.

The code written by arnelgp works perfectly in respect that it splits the string into its separate words but I do not want the word limited to be searched...

But i need the word limited to be in the record set

 

[theDBguy]

Than for the reply.... So the user will enter a company name such as "Someones Company Limited" but because i want it to search for someones and company and return any results with those two words in it also searches for limited which then returns lots of unwanted results.

The code written by arnelgp works perfectly in respect that it splits the string into its separate words but I do not want the word limited to be searched...

The first thing I would suggest then is to create a table of "unwanted/excluded" words. You would then be able to use it easily in your code.

 

[jdraw]

Charles,

I understand your plan to search, but what exactly is the purpose/end result of such searching?
Identifying/eliminating duplicates/replicates?
check for "alternate spellings"?
??

 

[CharlesDavenport]

Charles,

I understand your plan to search, but what exactly is the purpose/end result of such searching?
Identifying/eliminating duplicates/replicates?
check for "alternate spellings"?
??

Thanks for the reply,

The purpose for this is for the user to input new client and project data.

This information is then searched against a current and historic list of people the company has acted against and to check of there is a conflict to work for them.

One problem is that I need the full client name on the record including limited etc as if they become a new client then the database will add them to the current client list.

However, the current and historic client list has the full client name in it as well.

Many of the clients we have are repeat but under slightly different names ie they are a subsidiary or group company of a new client, that is why I need the search to look for each word in the clients name. Even so, if the user searches for limited then it will bring up every record with that name in it (which is 95% of the list and therefore the search is redundant).

i hope this makes sense...

 

[CharlesDavenport]

Charles,

I understand your plan to search, but what exactly is the purpose/end result of such searching?
Identifying/eliminating duplicates/replicates?
check for "alternate spellings"?
??

basically it produces a list of companies which we have acted for in the query (and report) which is the result of the search to inform the user there may be a conflict which then needs investigating...

 

[jdraw]

Many of the clients we have are repeat but under slightly different names

Or possibly misspellings of the same client name??

You might consider an authoritative client table with all the field components your need, along with coordinates (physical address, mailing address...). And some additional parameters/factors to confirm the client is/is not on of your authoritative clients. (Such as phone number, email,etc)
Depending on your business having a unique client list with unique clientID could be critical.

 

[CJ_London]

Or possibly misspellings of the same client name??

A few years ago I was asked to tidy up a clients customer list - approx 600k records - as there were duplicates and they also wanted to identify companies in the same corporate group i.e. add another layer of 'ownership'. Among other things I found McDonald's had been spelt 21 different ways.

 

[MajP]

See if this search makes sense. Here is 10k records with made up names. Start typing different things to filter the list. I find this extremely fast to find possibilities. The code to implement this class is a single line of code.

 

[Galaxiom]

Variations in spelling are best found using Damareau-Levenshtein Distance.
Essentially it measures the number of typographical glitches it would take to change one string into another
See the function in post 10 in this thread.

 

[MajP]

Variations in spelling are best found using Damareau-Levenshtein Distance.

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)

Matching logic

Probably not difficult but I would advise against it. In case where matches aren't exact, I would want someone to check the results rather than trust any algorithm I have a system for checking a batch of online payments by parents against the reference number for their child so it can be...

www.access-programmers.co.uk

I really think building a good search like the FAYT provides more utility. Or at least some implementation where you can refine the search as you go. A single search using Levehshtein is OK but your just as likely to find something with a *string*.

The fayt could be modified to do a *string* search for each different word and or them together, but not sure if necessary. Using the demo with 10k I have no problem searching for anything.

 

[arnelgp]

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).