Results 1 to 10 of 10

Thread: Please HELP

  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Thumbs up Unanswered: Please HELP

    I've made a table containing a title field and a description field.

    The title field is a text field and the description field is a memo field. the decription field will contain at least 14 seperate words.

    Does access allow me to search in both fields and the searching criteria can be in any combination of words or phrases, like on an online search engine. If access does not provide this feature, does anyone know if there is a third part software that has this feature.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Please HELP

    Originally posted by tau
    I've made a table containing a title field and a description field.

    The title field is a text field and the description field is a memo field. the decription field will contain at least 14 seperate words.

    Does access allow me to search in both fields and the searching criteria can be in any combination of words or phrases, like on an online search engine. If access does not provide this feature, does anyone know if there is a third part software that has this feature.
    Yes - You can use the LIKE command in a SQL query to search for fields that match a certain string. You can use wildcards on either end of the string to indicate that the string could be located anywhere within those 14 words.

  3. #3
    Join Date
    Mar 2004
    Posts
    24

    Re: Please HELP

    Originally posted by ss659
    Yes - You can use the LIKE command in a SQL query to search for fields that match a certain string. You can use wildcards on either end of the string to indicate that the string could be located anywhere within those 14 words.
    I'm sorry if I was not more clear. I am actually going to built a form so that user's that don't understand how to use queries can search this database. I am looking for a simple easy to use search engine. or possibly assembling one with a form. I just don't know where to start. Are there any examples out there. Please help.

  4. #4
    Join Date
    Mar 2004
    Posts
    7
    Here is an example of a Search Form...
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2004
    Posts
    24
    Originally posted by JFGambit
    Here is an example of a Search Form...
    The sample was not what i was looking for. In the database i'm a building, a user should be able to type in "Flat Head Screw" on a textbox, and the search will look for any description of flat head screw in the memo field. I tried doing a find next, but that only allows for on word.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by tau
    The sample was not what i was looking for. In the database i'm a building, a user should be able to type in "Flat Head Screw" on a textbox, and the search will look for any description of flat head screw in the memo field. I tried doing a find next, but that only allows for on word.

    I think you should take my suggestion of using LIKE in a SQL query. You could do :

    SELECT *
    from your_table
    where description like '*FLAT HEAD SCREW*'
    or title like '*FLAT HEAD SCREW*'

    You would put this code in the form, and dynamically place the term from the form where the word FLAT HEAD SCREW appears in this query. Try this query out yourself (just replace the your_table with your actual table name). This will do your search.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Access does not have the full-text search capability of SQL Server. Consider porting your database to the SQL Server platform.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by blindman
    Access does not have the full-text search capability of SQL Server. Consider porting your database to the SQL Server platform.

    Why does my suggestion not work? I just tried with a memo field putting 20 words, and could pull out 'FLAT HEAD SCREW' every time. Id like you to show me why my suggestion wont work!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your suggestion will work fine for "FLAT HEAD SCREW", but will not be able to find "SCREW, FLAT HEAD", "FLAT HEAD BRASS SCREW", or "FLAT HEAD SCREW" (with extra space character).

    Your suggestion is probably the best available for Microsoft Access, but does not have the capability of the SQL Server full-text engine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by blindman
    Your suggestion will work fine for "FLAT HEAD SCREW", but will not be able to find "SCREW, FLAT HEAD", "FLAT HEAD BRASS SCREW", or "FLAT HEAD SCREW" (with extra space character).

    Your suggestion is probably the best available for Microsoft Access, but does not have the capability of the SQL Server full-text engine.

    Ok you are right on the fact that using LIKE is not exactly a search engine-

    But for the original poster, keep this in mind. In any search engine, the more specific terms you put into the engine, the less results you will get. Try it with Google or any other - if you put in a broader term, you will get many more hits.

    So the point above is valid, and raises an important thing to remember when designing your database.

    1) Set up a form with pre-defined values to avoid extra white space characters in the middle of a string.

    2) Come up with a design spec from the beginning as to whether you will have descriptions like 'FLAT HEAD SCREW' or 'SCREW, FLAT HEAD'

    3) Even if you have 'FLAT HEAD SCREW' or 'SCREW, FLAT HEAD' or even 'FLAT HEAD BRASS SCREW' if your user searches for FLAT HEAD this query will pull back all of these results.


    Maybe SQL Server has this capability of full text- im not familiar with it. I do doubt however that you are willing to pay a huge license fee and take the time to migrate your data to SQL Server just as a workaround to this specific problem. If you implement some standarized design, you can do a lot with SQL. It works best with structured databases with good designs.

    Thats my 2 cents - make of it what you will!

Posting Permissions

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