Access 2010 SQL Query find partial match in string of full word only

Question

I hope this is a simple one, I just can't find how to get the result I want, maybe I am using the wrong keyword in SQL? I am searching an Employee table which contains a Full Name field, this field can be "Sam" or "Mr Evans" or "Mr Sam Evans" I am trying to find partial matches with another Names table which contains a Name field - this is usually a short name such as "Sam" or "Evans" but could be "Sam Evans" I have tried like, with and without * as follows ``` SELECT tblEmployee.FullName, tblNames.Name FROM tblEmployee, tblNames WHERE tblEmployee.FullName Like "*" & tblNames.Name & "*" ``` as well as the results I do want, this query can return Employee FullName of "Mr. Samson" which I don't want. I only want to see results contaning the full word "Sam" not FullNames with Sam in part of a word within the string. So I want to see Mr. Sam Evans and Mr. Sam Smith and Ms. Sally Evans, etc. I hope this makes sense. Many thanks for your help Edit - solution I have solved this - just postin…

Accepted Answer

Wildcards in SQL and access are a `%` symbol;

so

```
"SELECT tblEmployee.FullName, tblNames.Name FROM tblEmployee, tblNames
WHERE tblEmployee.FullName Like '%" & tblNames.Name & "%'"
```

Will match any instance of the string `tblNames.Name`

You can use the `%` at the start or end only to get the match of just the start of the string / end of the string repectively.

edit

Apologies, you can use `*` and `?` in access as you expect, however are you sending the query as a string to some VBA code? Or trying to run it directly? As directly in a query it'll not be able to parse the `tblNames.Name` string into the query and you'll need to pass that in from a form or other peice of code.

Edit based on comment

To select a specific word it's just a bit of a work around required:

```
SELECT * FROM table WHERE field LIKE '* myWord *' OR field LIKE '* myWord.*'
```

You can optionally caputure start of sentances `LIKE 'MyWord *' and word with commas, fullstops, exclamtion marks etc...

You could do an IN statement and have all the variations in a lookup table to keep it easy to maintain as another option.

Access 2010 SQL Query find partial match in string of full word only

Solution

Problem