Results 1 to 10 of 10

Thread: Contains Query

  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Unanswered: Contains Query

    I am trying to get a query whereby the user can search by a word, ie., "gel" and the query will return all records with the word contained in one field.

    I have a field "part description" and in the query I have created a field called question - which prompts the user for the keyword. I want the query to return, for example:

    Hair Gel
    Gel Pen
    Other Gel Item

    basically, any line containing the keyword.

    I have tried "like *[question]* in the query, but not getting very far!

    Any help, very much appreicated.


    Mike

  2. #2
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Mike,

    Try this:

    Like "*" & [question] & "*"


    Dean

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Like "*" & [Question] & "*"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ya beat me to it Friz
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    LOL... There are so few questions that I can actually answer, I figured I'd best jump on this one while I could!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    or this....

    Like """ & [Question] & """"

    (to handle words with ' in the them.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, but if you're gonna do that, you gotta get it right!!

    Like """" & [Question] & """"

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2008
    Posts
    25
    Thanks everyone the Like "*" & [question] & "*" works a treat!

    Thanks again.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by StarTrekker
    Yeah, but if you're gonna do that, you gotta get it right!!

    Like """" & [Question] & """"


    I did have it right. It's 3 " and then 4 " at the end. But I usually do it in a sql statement ("Select * from XXX where Field1 Like """ & something & """"). I'll test it as criteria in a query....

    Ok. I'm wrong. You're right. If it's not in a sql statement, you use 4 " and 4 ". Thanks for setting me straight ST.

    I claim stress, overwork, low-pay, dealing with kids, house caught on fire, car broke down, no alcohol, lack of any good drugs, failing health, and....(well, let me think up a few more). But really, I was just too #@#% lazy to test it and assumed it worked for criteria like this.
    Last edited by pkstormy; 11-21-08 at 05:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I claim stress, overwork, low-pay, dealing with kids, house caught on fire, car broke down, no alcohol, lack of any good drugs, failing health, and....(well, let me think up a few more). But really, I was just too #@#% lazy to test it and assumed it worked
    :lol: Classic! All perfectly good reasons ^.^

    If you are working inside a string, sql string building or not, it's 3... but then it's 3 on both sides... the only reason it ends in 4 is because your closing the string you're working in as well!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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