5

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 posting in case it might help anyone with a similar problem.

WHERE " " & tblEmployee.FullName & " " Like "* " & tblNames.Name & " *"

so basically by adding a space at the beginning and the end of the Employee Full Name, I catch all the correct records. I hadn't realised you could pad out a field like this but a colleague tipped me off!

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
RockyRoad
  • 51
  • 1
  • 1
  • 3

2 Answers2

3

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.

RemarkLima
  • 11,639
  • 7
  • 37
  • 56
  • Thanks for your reply. I am running this in an Access 2010 query for now to get the correct results. Then I will execute the SQL string in VBA code and insert the results in another table. The * is returning all fields with the Name as any part of it - but I want it to be specifically a complete word within the searched string. – RockyRoad May 28 '12 at 10:54
  • Just edited in light of your comments - basically you can use the spaces between the wildcards and the search term to filter it to a word if you follow. – RemarkLima May 28 '12 at 12:00
  • Thanks, I tried all these combinations too, but it still returns parts of strings e.g. Mr Samson Smith is returned as " Sam" is part of it, but if I only have " Sam " then Mr Simon Sam isn't returned because there's no space at the end. – RockyRoad May 29 '12 at 09:23
  • Sorry - hit return too soon! Is it possible to do it the way I want? Or do I need to figure out how to split the Name fields into separate complete words and store these in individual fields? – RockyRoad May 29 '12 at 09:26
  • Added solution to my initial post – RockyRoad May 29 '12 at 21:17
  • OK great, so as I said, the concept of using spaces to pad out the words is working as you'd expect. As I allueded to, you'll still need to capture " Sam." or " Sam!" or " Sam," or " Sam;" etc... if it's relevant to the fields that you're searching. Or didn't anything I suggest help at all? – RemarkLima May 30 '12 at 11:20
  • Hi RemarkLima, thanks for your suggestions, I knew the commas and fullstops would not be an option for my data and I had tried the * with and without spaces, so your suggestion didn't tell me anything new on this occassion. I just thought I'd post what did make it work, in case it's of interest to anyone else. What worked was adding the spaces before and after the actual field, rather then just the LIKE part of the statement - I didn't realise this could be done. – RockyRoad May 31 '12 at 10:57
  • I had said, but clearly wasn't clear enough. Best of luck with the rest of the project – RemarkLima May 31 '12 at 11:11
1

I used this solution from the edited post in Access 2007 and it worked perfectly!

WHERE " " & tblEmployee.FullName & " " Like "* " & tblNames.Name & " *"

Now my question is, what the heck is the SQL code doing and why does it work? Can someone break it down?

EDITED:

OK, my confusion arose from the different use of characters in Access SQL vs more standard SQL. & is concatenate in Access, while + is concatnate in standard SQL. And * is the wildcard for any number of characters in Access while it is % in SQL.

user3236990
  • 11
  • 4