Help with Expression/Criteria using the LIKE function with fieldnames and wildcards (Query)

[jenn]

I am trying to compare two columns in Access with a query and show when they don't match. The first column is called Sheet1.lastname and the 2nd column is called Sheet2.fullname.
Sheet1.lastname
Smith
Brown
Jones

Sheet2.fullname
Smith,George
Brown, Juliette
OtherPerson, John

I would like to change the criteria on the 2nd column so that it looks for the lastname value within the fullname column.

The SQL script looks like this:
Where ([Sheet2].[fullname]) Not Like [Sheet1].[lastname])); --> that only seems to function properly when the values are exactly the same, when I try to add the * wildcard for this:

Where ([Sheet2].[fullname]) Not Like [Sheet1].[lastname]*)); - I get a function error.

Any suggestions on how I can accomplish this?

[aytee111]

Not Like "*" & [Sheet1].[lastname] & "*"

That surrounds the field with wild cards, saying anything in front of the search and anything behind it. It is called string concatenation, making up what you want with quotes and &'s until it comes out looking how you want it.

[ssanfu]

I am trying to compare two columns in Access with a query and show when they don't match

If I understand correctly I would do this a little different.

Obviously, this isn't real data because trying to show names that don't match using only last names will not give you good results - Smith, John and Smith, Jim would match but they are different people.


Anyway, first create a query named "qrySheet1".
SQL would be

SELECT lastname FROM Sheet1;

Second, create a query named "qrySheet2".
SQL would be

SELECT IIf(InStr([fullname],",")>0,Left([fullname],InStr([fullname],",")-1),[fullname]) AS lastname FROM Sheet2;

Last, use the Query wizard to create an unmatched query. The SQL would look like:

SELECT qrySheet2.lastname
FROM qrySheet2 LEFT JOIN qrySheet1 ON qrySheet2.[lastname] = qrySheet1.[lastname]
WHERE (((qrySheet1.lastname) Is Null));

[jenn]

Not Like "*" & [Sheet1].[lastname] & "*"

That surrounds the field with wild cards, saying anything in front of the search and anything behind it. It is called string concatenation, making up what you want with quotes and &'s until it comes out looking how you want it.

**perfect - that is what I was missing!