Results 1 to 2 of 2
  1. #1
    chook is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    8

    Multiple Text Search on one Column to filter records

    HI

    I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong

    I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.

    Anyway... i found this post i thought might solve my problem:



    I have a form that searches through Item's names based on 2 keyword boxes.
    Here's the criteria in my query:

    Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"

    This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.

    Hope this helps,
    Evan

    This is pretty much exactly what i am looking for however i cannot get it to work!.. I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise. Can anyone see how it might be made to work.. or if it does maybe explain what i am doing wrong.. mine is like this:

    Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"

    Searchtable being my search form
    key1 being my first text box entry
    key2 being my second text box entry

    Thank you for your time.



  2. #2
    wrkadri is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    29
    First of all you need to apply these criterias in the query design view on both values:
    Key1 where Is Null or Like "*" & [forms]![Searchtable]![Key1] & "*"
    Key2 where Is Null or Like "*" & [forms]![Searchtable]![Key2] & "*"
    If you want to search for both textbox values, their data should match. Example:
    Joe ____ male _____ USA
    Emily __ Female ___ France

    Let's say that you want to search for Joe then you write joe and run query. If you are searching for name and gender, you should write joe and male or else you will get blank result.
    You can search by 1 textbox value if you are not sure of the second value or both textbox values if you sure about data you are searching for.

    Hopefully I answered your question.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 27
    Last Post: 01-21-2013, 02:43 PM
  2. Replies: 1
    Last Post: 09-17-2012, 11:42 PM
  3. Filter data by number as text column.
    By msadiqrajani in forum Access
    Replies: 14
    Last Post: 08-15-2012, 02:34 PM
  4. Search multiple fields from one text box
    By tommy93 in forum Queries
    Replies: 21
    Last Post: 01-10-2012, 02:18 AM
  5. Create filter for multiple field search
    By luvsmel in forum Forms
    Replies: 5
    Last Post: 01-02-2012, 11:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums