Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004

    Unanswered: sql query help...

    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.

  2. #2
    Join Date
    Jan 2004
    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.

    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.
    Attached Files Attached Files
    Last edited by -Dman100-; 07-11-04 at 17:46.

  3. #3
    Join Date
    Jan 2004
    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:

    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.

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    I don't know what operators Access has for string comparisons such as "begins with", but they can all be achieved using LIKE

    Search Type    SQL Condition
    -----------    -------------
    Contains       LIKE '%' || value || '%'
    Begins With    LIKE value || '%'
    Ends With      LIKE '%' || value
    Exact Phrase   LIKE value
    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.

  5. #5
    Join Date
    Jan 2004
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts