Selecting Partial String Matches (Full words only)

Question

A few days back I wrote a question here that got answered and helped me resolve what I thought at the time was my project:

Query only pulling in output from one column instead of all columns

This is what worked earlier and had no issues:

SELECT DISTINCT Product.Category, Analytics.Unique, Product.ID, 
Product.Family
FROM Analytics INNER JOIN Product ON Analytics.Search = IIF(Product.Category 
= Analytics.Search, Product.Category, IIF(Product.Field4 = Analytics.Search, 
Product.Field4,));

At the time, I only thought I only am required to look for exact matches, I have since found out that I need to join on partial matches as well where there is at least 1 full common word to both the strings that are being joined on. For example, if we are joining on "Beakers" vs "Huge Big Beakers" they should join because they have "Beakers" in common....

I have tried modifying my previous query from the other question to account for this based on some links I found here on stackoverflow, I keep getting Syntax Error and it does not really point to where the problem is. Here is my Query (it is only for 1 column, have not copy pasted it to all of them because it still does not work for some reason), I only am showing a few columns because they are repetitive anyways and to make it easier to read:

SELECT DISTINCT Product.Category, Analytics.Unique, Product.ID, 
Product.Family
FROM Analytics 
INNER JOIN Product ON Analytics.Search = IIF((SELECT DISTINCT 
Product.Category 
FROM Product, Analytics 
WHERE ' ' &  Product.Category & ' ' 
Like '* Product.Category *'), Product.Category, 
IIF(Product.Field4 = Analytics.Search, Product.Field4,));

something is wrong in this part that I tried to add because it worked before for the full match per the other question I had:

Product ON Analytics.Search = IIF((SELECT DISTINCT Product.Category FROM 
Product, Analytics WHERE ' ' &  Product.Category & ' ' Like '* 
Product.Category *'),

Answer 1

EDIT #1: Added sample of SQL that pads the fields to be searched with a space so it will look for whole words.

Not sure what your volume of data is, but the SQL I have included below works somewhat like you want it to. One flaw in your approach is the fact that when you are trying to locate 'words within a string', you can't pad a space on the front or back unless you pad both the search terms AND the string to be searched (normally, there are no trailing spaces in a text field).

I only did Field4 thru Field7, so you need to adjust.

NOTE: The first part of the criteria in the WHERE clause looks for an exact match; the second criteria looks for the padded word; and the third criteria looks for matches in your 'Fieldxx'

Here is SQL that pads each of the fields you search:

SELECT DISTINCT ' ' & [Category] & ' ' AS MyCat, Analytics.Unique, Product.ID, Product.Family
FROM Product, Analytics
WHERE (((' ' & [Category] & ' ')=[Analytics]![Search] 
Or (' ' & [Category] & ' ') Like "* " & [Analytics]![Search] & " *")) 
OR (((' ' & [Field4] & ' ') Like "* " & [Analytics]![Search] & " *")) 
OR (((' ' & [Field5] & ' ') Like "* " & [Analytics]![Search] & " *")) 
OR (((' ' & [Field6] & ' ') Like "* " & [Analytics]![Search] & " *")) 
OR (((' ' & [Field7] & ' ') Like "* " & [Analytics]![Search] & " *"));

Or, a really simple example:

SELECT DISTINCT Product.Category, Analytics.Unique, Product.ID, Product.Family
FROM Product, Analytics
WHERE (((Product.Category)=[Analytics]![Search])) 
    OR (((Product.Category) Like "* " & [Analytics]![Search] & " *")) 
    OR (((Product.Field4)=[Analytics]![Search])) 
    OR (((Product.Field5)=[Analytics]![Search])) 
    OR (((Product.Field6)=[Analytics]![Search])) 
    OR (((Product.Field7)=[Analytics]![Search]));

Answer 2

There are issue with your first IIF statement. IIF statement has 3 parts.

IIf ( experession , truepart , falsepart )

Expression is something you want to evaluate. it usually contains = ,> or < signs

lets take your first IIF statement:

iif((SELECT DISTINCT product.category FROM product, analytics WHERE ' ' & product.category & ' ' LIKE '* Product.Category *') , product.category,IIF(.....))

So your expression part in that contains this query:

( 
SELECT DISTINCT product.category 
FROM            product, analytics 
WHERE           ' ' & product.category & ' ' LIKE '* Product.Category *'
)

But you are not evaluating a result of that query, What are you trying to do with the result of that query? There are No =, > or < to evaluate result

you are also missing the false statement in last if statement. For More Explanation, Please visit: https://support.office.com/en-us/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3