Inserting wildcards at beginning and end of Access Search String?

Question

Working on a simple book database and working on a search feature. The search feature is working replacing all the spaces between words with wildcards, but need to insert wildcards at the beginning and end of the search string. How would I accomplish this?

Thanks in advance,
The Gambian Colonel

Ok, perhaps a little clarification. I'm using a text box to receive input from the searcher, then using a macro to load a form using that input in the WHERE statement. I'm using a replace function to insert wildcards instead of spaces (so "y t" becomes "y*t") but I need to find a way to turn it into ("*y*t*") so it searches in front and behind as well.

Now a bit of emphasis is required. I'm using Access. I'm using a text input box. This is NOT your standard type it and pray query. I'm taking input from the above mentioned box, performing a replace function on it to replace spaces with wildcards. I need to somehow expand or add an additional step where the text in the input box has wildcards added at the beginning and end of the string, so when the string is finally used in the where statement, all the program sees is *text*more*text* instead of text*more*text.

Answer 1

Okay, with your clarification this seems easy enough. Run your macro to Replace the spaces with *. Then put the result in a STRING variable. Then do this:

strWhatever = "Y*T"
strWhatever = "*" & strWhatever & "*"

That should do it.

Answer 2

[EDIT]

Ok, in addition to doing your Replace(), just concatenate an asterisk on the beginning and end of your string.

MyString = "y t"

MyString = Replace(MyString, " ", "*")

MyString = "*" & MyString & "*"






Use the % character and the LIKE keyword.

For example:

SELECT * FROM People WHERE FirstName LIKE 'Br%'

Will return any record where the first name starts with "Br" followed by any character (Brad, Brian, Brett)

Another example:
SELECT * FROM People WHERE FirstName LIKE '%Br%'

Will return any record where first name contains "br"--the string does not have to start with "br".

And finally:
SELECT * FROM People WHERE FirstName LIKE '%Br%'

Will return any record where the first name ENDS with "br".