Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Searching within multiple fields in an Access

Discussion in 'Information Technology' started by yourtrashhere@gmail.com, Jul 28, 2006.

  1. yourtrashhere@gmail.com

    yourtrashhere@gmail.com
    Expand Collapse
    Guest

    So basically, what I have is a bunch of words in one memo field, for
    example:

    dog cat cowboy tree flower

    To search it, this is the code I have now.

    ' Check for LIKE Last Name
    If Me.txtLastName > "" Then
    varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*" * "
    AND "
    End If

    The only problem is what I search for needs to be "in order", for
    example, if I search for dog, I'll get the table. But, if I seach for
    tree, I won't because tree was not place first. Can you please help me?
    Thanks a lot!
    Reply With Quote
     
  2. Loading...

    Similar Threads Forum Date
    World War 1 Sikh-Canadian Pioneers: Still Searching for Our Sikh War Heroes History of Sikhism Feb 20, 2011
    Sikh News US to begin searching Sikh turbans (Bangkok Post) Breaking News Sep 3, 2007
    Sikh News Sikh community condemns policy on searching turbans (Oakland Tribune) Breaking News Aug 29, 2007
    Searching for an answer Spiritual Articles Mar 22, 2006
    Soul-searching Doctors Find Life After Death Interfaith Dialogues Nov 15, 2004

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    "yourtrashhere@gmail.com" wrote:

    > So basically, what I have is a bunch of words in one memo field, for
    > example:
    >
    > dog cat cowboy tree flower


    Then you're violating the basic rule that fields should be atomic. Have you
    considered instead using Access as a relational database, and storing one
    keyword PER RECORD in a related table? You'll find it a much better design
    for many reasons!


    > To search it, this is the code I have now.
    >
    > ' Check for LIKE Last Name
    > If Me.txtLastName > "" Then
    > varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*" * "
    > AND "
    > End If
    >
    > The only problem is what I search for needs to be "in order", for
    > example, if I search for dog, I'll get the table. But, if I seach for
    > tree, I won't because tree was not place first. Can you please help me?
    > Thanks a lot!


    The * is a wildcard which means "match any string of characters". You have
    two wildcards, AFTER the txtLastName refererence; this will match the exact
    string at the start, and anything afterwards (and you are using belt and
    braces because you don't NEED two wildcards there).

    Put another wildcard before:

    If Me.txtLastName > "" Then
    varWhere = varWhere & "[LastName] LIKE ""*" & Me.txtLastName & "*"" AND "

    Thus if txtLastName contains cat, you'll get a criterion

    [LastName] LIKE "*cat*" AND

    (to be completed later in your code, I presume); and it will find records
    containing any string of characters, the letters cat, and then any other
    string.

    Note that this will include "muscatel" and other words containing the
    substring cat.

    John W. Vinson/MVP
     
  4. yourtrashhere@gmail.com

    yourtrashhere@gmail.com
    Expand Collapse
    Guest

    That's not really what I meant. Basically, I have 2 fields:

    |NAME|Tags |
    |Bob |cat dog flower|

    I need to search the tags for a word. Not necessarily cat. Just
    anything. The code I have will only allow me to search for cat, but I
    want to be able to search for "flower" to bring up the same record.
    Thanks a lot for your reply though!
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 16 May 2006 06:37:14 -0700, yourtrashhere@gmail.com wrote:

    >That's not really what I meant. Basically, I have 2 fields:
    >
    >|NAME|Tags |
    >|Bob |cat dog flower|
    >
    >I need to search the tags for a word. Not necessarily cat. Just
    >anything. The code I have will only allow me to search for cat, but I
    >want to be able to search for "flower" to bring up the same record.
    >Thanks a lot for your reply though!


    LIKE "*" & [Enter keyword:] & "*"

    then. My apologies for answering the question that you asked, rather
    than the question that you intended.

    Note that my criticism of the structure of the Tags field still
    applies. You have a one (name) to many (tags) relationship; rather
    than embedding the many tags into a single field, you would do much,
    much better to have a properly normalized structure:

    Names
    PersonID <<< Primary Key, *not* a name since names aren't unique
    PersonName <<< NAME is a reserved word, Access will get confused
    <other info about the person or entity>

    Tags
    Tag <<< list of valid tag values for use in a Combo Box

    NameTags
    PersonID
    Tag

    You'ld have records like

    Names
    123
    Bob
    <other info about Bob>

    Tags
    flower
    cat
    theramin
    widget

    NameTags
    123 flower
    123 cat
    123 widget


    John W. Vinson[MVP]
     

Share This Page