Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: More complicated search

    Is there any way you can set a Combo box to search in the following way:

    For example typing in lig ho brings up a list showing records such as Light House, house light, The Lightening Hotel etc. Hope I've explained this well enough, cheers.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Fuerteventura
    Is there any way you can set a Combo box to search in the following way:

    For example typing in lig ho brings up a list showing records such as Light House, house light, The Lightening Hotel etc. Hope I've explained this well enough, cheers.
    Hi
    Not really. You could type into a text box and then have the before update event create a filter on the combo e.g.
    Code:
    Me.MyCombo.ReowSource = "SELECT Foo FROM TableBar WHERE Foo LIKE '*" & REPLACE(Me.MyTextBox, " ", "*") & "*'" 
    Me.MyCombo.Requery
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can, but it will require a bit of custom coding. If I were to tackle this requirement, I would provide a text box for the criteria and a command button to filter the combo box. In the onclick event of the command button, I would use the Split() function to construct a filter clause for the combo box, then requery the combo box.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    You can, but it will require a bit of custom coding. If I were to tackle this requirement, I would provide a text box for the criteria and a command button to filter the combo box. In the onclick event of the command button, I would use the Split() function to construct a filter clause for the combo box, then requery the combo box.
    Soz - yup - Teddy spotted the house light that I missed
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2005
    Posts
    196
    thank you both very much for that - will start experimenting, cheers.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ---- got stuck on the phone, so i see my learned colleagues have already given some feedback. here is what i was going to say:


    you can!
    but are you sure you want to do this?

    parse the 'lig ho' into 'lig' and 'ho'
    either "manually" looking for spaces, or using Split() also looking for spaces - lets imagine this ends up in an array frags(1...N)

    dim strSQL as string
    dim pointer as integer 'hope you wont need a long!!!
    dim frags() as string
    ....do whatever to get the fragments into (redimmed) frags()
    strSQL = "SELECT * FROM blah WHERE ((1=99) "
    for pointer = 1 to ubound(frags)
    strSQL = strSQL & "OR (this LIKE '*" & frags(pointer) & "*') "
    next
    strSQL = strSQL & ");"

    given typical user misbehaviour
    e.g. user types in 'l i g h o'
    the query could gum up your system for a while and return most of your records...

    ...so i don't let my users have this option: my searches accept any contiguous string so i can get away with ....LIKE '*" & userInput & "*'....

    if i want to let them look for 'lig' AND 'ho' i make two (or if i'm feeling generous, three!) separate txt boxes and force the users to do the work.
    me-the-coder doesn't need to handle the Split(), and the potential damage a user can do is mitigated.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2005
    Posts
    196
    Many thanks for your reply - I'm a bit new to this game so it's probably best if I ask in stages:

    First of all - I'll have made a majore breakthrough if I can do the following:

    If in txt1 I type lig ho, I'd like txt2 to show lig and txt3 to show ho. It doesn't matter if I execute it with a command button or on the text box change function, cheers.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    To expand on Izy's suggestion - which will do the job

    your sql will lookm something like

    select blah from blah where
    mycolumn like "*LI*" or mycolumn like "*HO*"

    how you implement that is up to you as the designer of the app.

    my personal preference would be a list box, adding each criteria as entered

    eg you rows in the unbound listbox are
    li
    ho

    -when your users presses the search button then that s converted to mycolumn like "*LI*" or mycolumn like "*HO*"

    the advantage of this approach is that you coudl add further user choice by allowing the use of NOT

    eg
    (mycolumn like "*LI*" or mycolumn like "*HO") AND mycolumn NOT LIKE "*LIC*"

  9. #9
    Join Date
    Aug 2005
    Posts
    196
    many thanks for your help - I think sql will be the only real way of doing this - I should be able to sort it if I can just work out how to seperate 'Li' and 'ho' from the test box, cheers.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Fuerteventura
    many thanks for your help - I think sql will be the only real way of doing this - I should be able to sort it if I can just work out how to seperate 'Li' and 'ho' from the test box, cheers.
    Split(). This is of course assuming you have a version of access recent enough to have the split function...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Aug 2005
    Posts
    196
    Thanks for your reply.
    so the code would be something like:

    Code:
    split() text2.text
    ??

    How would I then assign variables based on the two words that have been split?

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The split function:

    Returns a zero-based, one-dimensional array containing a specified number of substrings.

    So I guess you have to use an array to hold the results of the split function...




























    pssssssssssst: arrYourArray = split(someDelimitedString)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Aug 2005
    Posts
    196
    I'm sorry Teddy - being new to this I'm not very good at taking explanations and interpreting into code! Is there any chance you could give me some example code? That way I can manipulate it which helps me learn what each line does - cheers.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Take a look at "using arrays". There's good stuff about how to receive and work with arrays, which is what the Split() function returns. Understanding arrays will also substantially increase your basic vba skillset...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Aug 2005
    Posts
    196
    I've had a look but as with most MS help files, I can't make head or tail of how it relates to what I want to do

Posting Permissions

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