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

    Unanswered: Filter for a Keyword search

    Hi guys, I am a sort of a beginner on Database design. At the moment, I am stick on designing a filter for my database, hope someone can kindly offers help. Thanks in advance.

    The Problem:
    It is a database created for recording varies science projects that have been sent to my place and get reviewed, each projects are individual, and they can be any kinds of science project. So each project will be enter as a record with a mark indicating how good it is and the contact details of the person who sent in the project. At this point, designing the form and record are easy, but the hard bit is about the “Key Words”, the “Key Words” are some key terms or the content of the science project, so I can use it to search for projects that related to a set of topics. This is where I am stuck and need some guideline or advise from you guy.

    I have two tables: “Project” and “Keyword”

    Table “Project” has the following fields,
    [projectno] – has relationship with the table “Keyword”
    [recorddate]
    [score sum]
    [score product]
    [contact name]
    [contact address]

    Table “Keyword” has the following fields,
    [Keywordno] – ID no for Keywords
    [projectno] – ID no for the project which has a relationship with the table “Keyword”
    [keyword] – keyword itself

    How do I setup a Marco or a Module to apply a filter to the database?
    For example, I want to setup a Marco or module that will enable me to enter a word e.g. “drug” in a form or pop-up box, and by simply click a command button will allow me to apply the filter to the database and show all the filtered records in form and queries table.

  2. #2
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    It's easier than you think!

    1. Create a one-to-many relationship between your 2 tables based on the projectno. For speed, ensure your projectno and keyword fields are indexed (duplicates okay on the keyword).

    2. No need for a macro. (You can write this in VBA as well). Build yourself a form with the textbox on there. Go to properties of the text box and name it... ie. txtkeyword

    3. Save the form and open a query. Add the fields you want in your query. Add the keywords field but uncheck the show. Within the Criteria, type the following:
    Like "*" & [Forms]![Yourformname]![txtkeyword] & "*"

    4. Add a button to your form to execute the query. Type a keyword in your form and execute the query. Voila...

    Now tidy it up... I would leave spaces in front and at the end of EVERY keyword... and then change the criteria to:
    Like "* " & trim([Forms]![Form1]![txtkeyword]) & " *"

    Trim will trim any spaces off the front and end of the keyword your user writes in. The space after and before the * will ensure it's looking for a word that's independent in your keyword field.

    Some folks may give me a hard time about my simple approach up here... oh well. You said you're a beginner!
    Doug

  3. #3
    Join Date
    Jan 2004
    Posts
    5

    Thanks, this has been great help

    Thanks so much for the filter thing, it actually has enable me to carry on to design others stuff. Many appreication, Pattop

  4. #4
    Join Date
    Jan 2004
    Posts
    5

    Re: It's easier than you think!

    Hi, after I have setup the filter. I am having a bit of trouble on setting a condition in a Macro, just wondering if you know how.


    This is my condition:

    [Forms]![Search]![txtkeyword] < (2characters)

    *the "2charaters" is a invalid string*, do you know how to express "2characters" under MS access, if you can answer this, it would be very helpful, thanks in advance

    Pattop

    Originally posted by coders4hire
    1. Create a one-to-many relationship between your 2 tables based on the projectno. For speed, ensure your projectno and keyword fields are indexed (duplicates okay on the keyword).

    2. No need for a macro. (You can write this in VBA as well). Build yourself a form with the textbox on there. Go to properties of the text box and name it... ie. txtkeyword

    3. Save the form and open a query. Add the fields you want in your query. Add the keywords field but uncheck the show. Within the Criteria, type the following:
    Like "*" & [Forms]![Yourformname]![txtkeyword] & "*"

    4. Add a button to your form to execute the query. Type a keyword in your form and execute the query. Voila...

    Now tidy it up... I would leave spaces in front and at the end of EVERY keyword... and then change the criteria to:
    Like "* " & trim([Forms]![Form1]![txtkeyword]) & " *"

    Trim will trim any spaces off the front and end of the keyword your user writes in. The space after and before the * will ensure it's looking for a word that's independent in your keyword field.

    Some folks may give me a hard time about my simple approach up here... oh well. You said you're a beginner!

  5. #5
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40
    This is a little trickier for a beginner. If you only want to use 2 characters, you can set the criteria to:

    Like "* " & len(trim([Forms]![Form1]![txtkeyword]),2) & " *"

    IF you know all the combinations that folks would be searching for, you should use a combo box or list box instead of the text field. Example... if it's State. You could have a table of all the state abbreviations and populate it in the combo box.

    IF you want folks to get a warning if they put more than 2 characters in it, then you'll have to create an event as they key in the value. Each key press, check the length of the field.

    NOTE: I've not tested any of this....just going by memory.
    Doug

Posting Permissions

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