I'm trying to write a sql query that will accomplish the following.

I have a web form (search.asp) with a text field and button to submit the form to a results.asp page. The user can input any string of characters into the text field on the search.asp page and when the user hits the search button I want to query the database and return the closest matches or an exact match if one exists from a single table and column (prod_number) field in the Access database. I don't want the query to return all entries in the column, but rather search for the closest matches entered in the string that was searched for and pull only those entries in the database that are similar to the string.

I was trying to use wildcards after the LIKE operater, but couldn't get it to work properly?

SELECT prod_number
FROM tblProducts
WHERE prod_number LIKE

I'm not sure if this is the correct way to write the sql statement for what I'm trying to accomplish. Can anyone offer any help.
Thanks.
-Dman100-

I'm really a novice at writing sql queries, so any guidance is greatly appreciated.

Here is a link in order to see what I'm trying to do. I have also attached the test database as an attachment.

http://www.dwayneepps.com/test/search.asp

All I have for the sql statement on the results.asp page is:

SELECT *
FROM tblProducts

which returns all the entries in table. Can anyone offer any help on how to
write the sql statement to find the closest matches that are entered in the
text field on the search.asp page?

Thanks for any help.
-Dman100-

Okay, I know I need to qualify a variable to tell SQL what to do. So, if I have a text field and and a menu field on a form, then I will have
two name/value pairs. One for the text field and one for the menu. The
text field will just have a string value. I'm trying to determine what
values I would need for the menu under the following circumstances.

Here is a test URL to see what I'm trying to do:

http://www.dwayneepps.com/test/search.asp

I have "contains" for finding the string anywhere in the enteries of the
database search
I have "begins with" for finding the string only at the beginning of the
enteries of the database search
I have "ends with" for finding the string only at the end of the enteries of
the database search
and finally
I have "exact phrase" for finding the exact match of the string for the enteries
of the database search.

Right now, I have the value set to AND for "contains", "begins with" and
"ends with". Just as a placeholder. I have EXACT as the value for "exact phrase".

My question is:
Using the above two vaules from the form, what would be the right value to use for the menu in order to setup the correct SQL statement? Would I need to use wildcard values for the menu values?

For the SQL statement, I'm thinking it would be something like:

SELECT prod_number
From tblProducts
Where prod_number LIKE (textfield value variable) AND followed by the remaining part of the expression using wildcards from the values set in the menu (second variable).

I hope that makes sense? I'm in the infant stages of learning SQL so please forgive my lack of a clear explanation?

Any help is appreciated. Thanks.
-Dman100-

I don't know what operators Access has for string comparisons such as "begins with", but they can all be achieved using LIKE

where "||" means concatenate - the syntax may be different in Access, e.g. CONCAT(a,b) instead of a||b

So you can either manipulate your value and just use "LIKE value", or you can run a different select statement with the appropriate conditions and the value as given.

Hi Tony,
Thanks for replying to my post. I appreciate the help. Would it work by setting the values for the menu items as follows:

contains: '%' || textfield_name || '%'
Begins with: textfield_name || '%'
Ends with: '%' || textfield_name
Exact: textfield_name

where textfield_name refers to the value set by the string entered in the text field. So, the value of the menu items places the value from the text field into it. Then in the SQL statement I would only qualify the variable for the select menu field.

Thanks again for your help.
-Dman100-