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?
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.
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.
I have "contains" for finding the string anywhere in the enteries of the
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
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:
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?
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.