SQL Query Criteria Syntax

Question

I have a question on a select query that I'm running within a MS Access database that's comparing two tables. My query returns blanks if I type in the criteria section:

Like "*[Name of field]*" 

However I get the expected results if I type the below instead:

Like "*" & [Name of field] & "*"

Can anyone explain me the difference?

Thanks!

Answer 1

"*[name of field]*" gets interpreted exactly as written, where as "*" & [name of field] & "*" interprets the field, takes its value, and concatenates the leading and trailing *. So if the value of [name of field] is "something", then

"*[name of field]*" --> "*[name of field]*"

"*" & [name of field] & "*" --> "*something*"