#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Posts
    6
    Rep Power
    0

    Basic Sql Select Need Help Baaaad


    I have been working on this problem off and on for over six months. I have a table of recipes. Using the Name1 column.

    Name1
    ------------------------------------------------
    Baked Cauliflower and Mushrooms
    ------------------------------------------------
    Baked Fish with Tomatoes and Spices
    ------------------------------------------------

    Using the statement:

    Select Name1 FROM RECIPES WHERE
    Name1 = '*' or Name1 LIKE '%%*%%'.

    When * is one word such as 'Baked' or 'Fish' it works fine
    When * are words next to each other such as 'Baked Fish' or 'with Tomatoes' or 'and spices' it works fine.

    Here is the problem when * are words not next to each other such as 'Baked Tomatoes' or 'Fish Spice' or 'Baked Spice' nothing is returned.

    I am using Access 2.0 and considering MySql. I dont want to split up the Name1 column. I would prefer to continue using Access 2.0
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    Here is the problem when * are words not next to each other such as 'Baked Tomatoes' or 'Fish Spice' or 'Baked Spice' nothing is returned.


    Actually your query is doing right thing, picking up data correctly.

    If you cange the query a bit then you will get the result you are looking for.first split the search word based on space and put it in a loop and create a query like this:


    Select Name1 FROM RECIPES WHERE

    //loop should start here for the searching words

    Name1 LIKE '%word1%' AND

    Name1 LIKE '%word2%'

    if both the words present in the column then only the result will be returned.That is the one way of doing it.you can try with different sql query combinations.
    SR -
    webshiju.com
    www.lizratechnologies.com

    "The fear of the LORD is the beginning of knowledge..."
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Posts
    6
    Rep Power
    0

    Basic Sql Select


    Can this be done in Access and which versions. I really need to know exactly how to split the input.
  6. #4
  7. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    Can this be done in Access and which versions. I really need to know exactly how to split the input


    Yes ,it can be done in Access.i don't know which languge you are using to write front end.if you can tell me that then i can help you to create that sql query..
    SR -
    webshiju.com
    www.lizratechnologies.com

    "The fear of the LORD is the beginning of knowledge..."

IMN logo majestic logo threadwatch logo seochat tools logo