Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2007
    Posts
    29

    Unanswered: Query from text box string

    I really thought there would be a good piece of canned code for this. Since its mostly string manipulation.

    I have a text description field that is kind of a catch all for things that don't really fit anywhere else in the DB. I want to search that field using a text box for search criteria. I want to do a smart search so that something like red ring will find anything with red and anything with ring in it. Ignoring spaces and order and other such things.

    Anyone have this already done anywhere?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this thread might be what you want
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    29
    Good post but I need multiple keywords.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Then this might be your only chance.

    What you want to do seems too advanced for Access capabilities; are you sure there's not a better method?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2007
    Posts
    29
    Its not too advanced its just a ton of string manipulation that I was hoping to find already built somewhere. I guess ill just write and be done with it. Ill try and put a sample back up when im done.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you can provide a working sample to solve this problem then I personally (and I'm sure others too) would be very appreciative of seeing the solution!

    I'm unaware of any working method of changing the colour of a keyword in a textbox whilst leaving the rest of the text a different colour.
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2007
    Posts
    29
    AHH I see the problem now. Its not a coloring question its a simple multiple keyword search. which will return the full record to a listbox. My use of red was meant as a keyword not a color. I assumed you sent me to those links because they dealt with searches.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh I see, you just want to perform a multiple keyword search!

    If you searched for: "My name is George"
    Would you want to return results that contained any of the above words in any order, all of the above words in any order, all of the above words in the order specified, etc..?
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2007
    Posts
    29
    Ideally it would do all keywords in any order. It was obviously much too early in the morning for me to be asking questions Sorry for the confusion.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any limit to the number of keywords?

    We're going to end up in the realms of dynamic SQL shortly, so the more information we get up-front the better
    George
    Home | Blog

  11. #11
    Join Date
    Jul 2007
    Posts
    29
    Realistically I cant see it extending much past 3 maybe 4 keywords. If needed I would be comfortable with an artificial cap of say 4.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, well to perform our search we have to construct an elaborate WHERE clause.
    To do this, we first have to identifiy how many clauses we have to add; i.e. how many keywords there are.
    givign it a cap means we can loop exactly 4 times (only consider the first 4 words regardless of what's after it) OR we can give it an unlimited amount and loop through our search string to break it down into n pieces.

    Armed with this number in a variable we then set about making a second loop to construct our where clause.

    Untested pseudo code
    Code:
    Dim fullSearchString As String
    Dim strWhere As String
    dim startPos As Integer
    
    startPos = 1
    fullSearchString = Me.MySearchBox.Value
    
    strWhere = "WHERE"
    For x = 0 to MaxValue
        strWhere = strWhere & " Field1 LIKE '%"
        strWhere = strWhere & Mid(fullSearchString, startPos, Instr(startPos, " ",fullSearchString))
        strWhere = strWhere & "%' AND"
        startPos = Instr(startPos, " ",fullSearchString))
    Next x
    
    strWhere = Left(strWhere, Len(strWhere) - 3) 'remove last AND
    
    MsgBox strWhere
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    alternative route for an arbitrary number of words (and using the 'classic' Access wildcard):

    dim manyWheres() as string
    dim x as integer
    dim strWhere as string
    manyWheres = split(me.someTextBoxName, " ")
    for x = 0 to ubound(manyWheres)
    strWhere = strWhere & "someFieldName Like '*" & manyWheres(x) & "*' And "
    next x
    strWhere = "WHERE " & left$(strWhere, len(strWhere) - 4)
    msgbox strWhere

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Split! that was the function I was looking for!
    and darnit, I used Left() instead of Left$() again.

    Furthermore, I can never remember which wildcard access likes... I believe VBA prefers as asterix (*) wheras JET would like to see the percent symbol (%).
    George
    Home | Blog

Posting Permissions

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