I have an access database that contains information about books - title, author publisher.
I am trying to make a search form that allows someone to choose whether they want to match the whole word or go for a partial match. A partial match is easy:
SELECT Author FROM Books WHERE Author LIKE '%Adam%'
Let's say this returns two records:
1. Douglas Adams
2. Adam Smith
How can I modify the SQL so that if the user specifies match whole word on the form and they search for 'Adam' it will ignore 'Douglas Adams' and only show 'Adam Smith'.
I am wary of putting spaces on either side of the search term because the name might appear at the start, middle or end.
eg SELECT Author FROM Books WHERE Author LIKE '% Adam %' wouldn't work because there is no space before 'Adam Smith'.
Is it possible to do something like
SELECT Author FROM Books WHERE " " & Author & " " LIKE '% Adam %'
or would this either not work at all or sometimes miss results?
Thanks very much!
Ed
I am trying to make a search form that allows someone to choose whether they want to match the whole word or go for a partial match. A partial match is easy:
SELECT Author FROM Books WHERE Author LIKE '%Adam%'
Let's say this returns two records:
1. Douglas Adams
2. Adam Smith
How can I modify the SQL so that if the user specifies match whole word on the form and they search for 'Adam' it will ignore 'Douglas Adams' and only show 'Adam Smith'.
I am wary of putting spaces on either side of the search term because the name might appear at the start, middle or end.
eg SELECT Author FROM Books WHERE Author LIKE '% Adam %' wouldn't work because there is no space before 'Adam Smith'.
Is it possible to do something like
SELECT Author FROM Books WHERE " " & Author & " " LIKE '% Adam %'
or would this either not work at all or sometimes miss results?
Thanks very much!
Ed