an SQL query to search partial matches

[aa]

what would be syntax for an SQL query to search partial matches, compatible
with Access?
I tried WHERE (fieldname LIKE "something") - it did not cause an error, but
returns an empty recordset though there are entries in the field containing
"something" as substring

 

[Douglas J. Steele]

Like requires wildcards for partial matches.

Depending on whether you're using DAO or ADO, try WHERE (fieldname LIKE
"*something*") or WHERE (fieldname LIKE "%something%"). That'll find those
records where the word something appears anywhere in the field. If you want
only those records starting with something, you'd use WHERE (fieldname LIKE
"something*") or WHERE (fieldname LIKE "something%"). For only those records
ending with something, you'd use WHERE (fieldname LIKE "*something") or
WHERE (fieldname LIKE "%something").

There's also _ with DAO (? with ADO) which stands for 1 character. In other
words, WHERE "s*n" would find sun, son, soon and san (among others), while
WHERE "s_n" would only find sun, son and san, not soon.

Check Like in the Help file for more possibilities.

 

[aa]

Thank you very much, it works