Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    40

    Unanswered: advanced word search filtering

    hi.

    I am looking for word search to filter out the records based on the certain words he want to look for.

    How should i write the code in the event when he click the button to filter out the records.

    for etc, " msc jessica " or " jesscia tan ", when he input word " jessica " and click button, and automatically limit the records containing the word " jessica "

    for more etc again, " mr tan " " mrs tan ", " mrs jane ", " mrs jones ", when he input "mr", then it automatically limit all the records that contain " mr"

    Hope u understand. the problem is that how i should write the code " when person input the any words , to limit the records "

    Here the code based on wildcard.

    Quote

    stDocName = "Search"

    stLinkCriteria = "[Project] like '" & Me![txtSearch] & "*'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Quote

    Please helps.

    Thanks.

    Cheers.
    Last edited by xeroleacy; 11-09-05 at 23:09. Reason: not being clear. :)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You basically already there

    the sql clause to use is, as you are correctly using, 'like'
    you need to a wildcard character (*) to the front, and / or back, ofthe word you are looking for

    specifying "*mr*" will retrieve all records which have mr in the column. eg mr, mrs, amr, mrx

    specifying "Mr*" will retrieve columns starting with MR. eg mr, mrs, mrx but NOT amr

    specifying "*Mr" will retrieve columns with ending with MR. eg mr, amr but NOT mrs, mrx

    specifying "*Mr *" will retrieve columns with words ending in MR, whihc I suspect is what your coursework requires

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And here's a related link (although it's geared toward generating SQL statements for ASP Web pages):

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-se...iplefields.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jun 2004
    Posts
    40
    hi. healdem,

    i am sorry not being too clear with this.

    i mean that in any words that he input, will retrieve the records that contain same words.

    For etc, " A Big elephant", " A Small Cat ", " A tiny Cat ", " A Small elephant"

    When a person input, " A ", will return 4 records.

    When a person input, " Big ", will return 1 records

    When a person input, " Cat", will return 2 records.

    Hope u understand.

    I don`t think that using " like " can solve this ?

    Because i am doing the " search " function, to allow person to input the word ( for etc, input cat, will return 2 records ) or any wildcard containing ( for etc, a person input "ele", will return 2 records, and another etc, input "tin", will return 1 records. )

    Is it possible to do combine both into one. ?

    Hope u can understand.

    Cheers.


    Quote Originally Posted by healdem
    You basically already there

    the sql clause to use is, as you are correctly using, 'like'
    you need to a wildcard character (*) to the front, and / or back, ofthe word you are looking for

    specifying "*mr*" will retrieve all records which have mr in the column. eg mr, mrs, amr, mrx

    specifying "Mr*" will retrieve columns starting with MR. eg mr, mrs, mrx but NOT amr

    specifying "*Mr" will retrieve columns with ending with MR. eg mr, amr but NOT mrs, mrx

    specifying "*Mr *" will retrieve columns with words ending in MR, whihc I suspect is what your coursework requires

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by xeroleacy
    I don`t think that using " like " can solve this ?
    Why do you think that? What you describe is *exactly* what 'Like' does.

    What have you tried already? What did it do? What didn't you like about it?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your where clause becomes.....

    where blah like "*ele" or blah like "*tin*"
    will return any row where blah contains ele or tin
    eg "the elephant" "the tin man" or "martinique"

    where blah like "*ele" AND blah like "*tin*"
    will return any row where blah contains ele AND tin
    eg "the elephant shat in the tin bath"

    you need to do some preporocessing on the word(s) entered by the user. Thats down to your user interface design. youl could use a series of combo boxes allowing the user to specify in detail what they want

    eg
    not like "*blah*"
    not like "elephant*"
    like "*ele" and not like "ele*"

    the world is your oyster, and the user is the grit that irritates so much that you produce pearls.

  7. #7
    Join Date
    Jun 2004
    Posts
    40
    hi healdem,

    U are right. and i apologies.

    Because i do not understand how " like " works due to the my design interface that caused to return only one record, not all records . that make me think like this.

    For etc, when i input jessica or jess, it return the 1 records, " mrs jess " although i have few records containing " jessica "

    Look like i have to redo the design.

    Thanks and cheers.

    p/s i tend to learn very fast and do not understand the basic of access. -_- so i am newbie learning.



    Quote Originally Posted by healdem
    so your where clause becomes.....

    where blah like "*ele" or blah like "*tin*"
    will return any row where blah contains ele or tin
    eg "the elephant" "the tin man" or "martinique"

    where blah like "*ele" AND blah like "*tin*"
    will return any row where blah contains ele AND tin
    eg "the elephant shat in the tin bath"

    you need to do some preporocessing on the word(s) entered by the user. Thats down to your user interface design. youl could use a series of combo boxes allowing the user to specify in detail what they want

    eg
    not like "*blah*"
    not like "elephant*"
    like "*ele" and not like "ele*"

    the world is your oyster, and the user is the grit that irritates so much that you produce pearls.

Posting Permissions

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