Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181

    Multiple Field Keyword Search


    On one of my forms i have an unbound textbox and a listbox(data supplied to the listbox by a query). In the query i have used certain keywords so the user can type these keywords in to the textbox and the listbox is filtered. is there a way that i can use the one textbox to enter multiple keywords at the same time to search across multiple fields. For example: Coates, PhD. The listbox should then filter to to show only PhD students in the Coates building. At the moment if i use a space or comma in the search box the listbox just goes blanks. Be kind with your feedback please and thank you for your support.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it can be done, but depends how flexible it needs to be - how does the app know which fields to apply the criteria to. If it is just two, say StudentName and Level and always in the same order and you are not using lookup fields in your table design you could do something like


    Code:
    dim I as integer
    dim arr() as string
    arr=split(txtSearch,",")
    sqlStr=SELECT * FROM myTable WHERE StudentName='" & arr(0) & "'"
    if ubound(arr)>0 then sqlstr=sqlStr & " AND Level='" & arr(1) & "'"
    listbox.rowsource=sqlstr

  3. #3
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Hi Ajax

    i am not using lookup fields in this particular table that the listbox gets its data from. I need the listbox to load with data from the query then search, has suggested by the code you have kindly provided. Then use a search reset button to clear the search keywords and then populate the listbox with the data that the listbox is getting from the query recordsource. I hope this makes sense. I will try your code now to see what happens. Thanks again for the speedy response.

  4. #4
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by Ajax View Post
    it can be done, but depends how flexible it needs to be - how does the app know which fields to apply the criteria to. If it is just two, say StudentName and Level and always in the same order and you are not using lookup fields in your table design you could do something like


    Code:
    dim I as integer
    dim arr() as string
    arr=split(txtSearch,",")
    sqlStr=SELECT * FROM myTable WHERE StudentName='" & arr(0) & "'"
    if ubound(arr)>0 then sqlstr=sqlStr & " AND Level='" & arr(1) & "'"
    listbox.rowsource=sqlstr

    Hi Ajax

    tried your code but got error message "Invalid use of Null" on this line "arr=split(txtSearch,",")". I have changed the relevant lines of the code for the table i use, name of the textbox and listbox. I put the code in the change event of the textbox. Although, i would like the keyword search like. "Coates,PhD, etc and for the listbox to filter but if i have to make major changes then i will give it a miss. Thanks for all your feedback.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I can't resolve issues with my aircode - you need to provide the code you are actually using. but if you have put it in your change event, the field has not been updated so you need to use txtSearch.text

  6. #6
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by Ajax View Post
    I can't resolve issues with my aircode - you need to provide the code you are actually using. but if you have put it in your change event, the field has not been updated so you need to use txtSearch.text
    Hi Ajax

    as you can see the code i am using is very simple

    "Private Sub SearchFor_Change() Me.SearchResults = Me.SearchResults.ItemData(0)
    Me.SearchResults.Requery
    End Sub

    SearchFor is the name of the textbox and SearchResults is the name of the Listbox. All of the keywords that have set up are in the Query that is used to populate the Listbox when the form loads. Hope this makes more sense.

    Click image for larger version. 

Name:	Query Keywords.JPG 
Views:	34 
Size:	44.8 KB 
ID:	37695Click image for larger version. 

Name:	frmPeopleSearch.JPG 
Views:	37 
Size:	62.2 KB 
ID:	37696

  7. #7
    istari88 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Kentucky, USA
    Posts
    13
    Looks like you are matching partial strings across multiple fields. If you cannot use multiple search boxes (1 for each field) then maybe you are able to concat. the fields to 1 field and search the long string for matches (or partial matches). Need the right search criteria words LIKE and others. Concat within the table as a calculated field: Str$([F1]) & Str$([F2]) & Str$([F3]).

  8. #8
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by istari88 View Post
    Looks like you are matching partial strings across multiple fields. If you cannot use multiple search boxes (1 for each field) then maybe you are able to concat. the fields to 1 field and search the long string for matches (or partial matches). Need the right search criteria words LIKE and others. Concat within the table as a calculated field: Str$([F1]) & Str$([F2]) & Str$([F3]).
    Hi istar
    Not sure I understand our would know how to do the last option. Could you give me an example. The first option, although not pretty is the way to go. One box, to filter building names. Next box, to filter PhD or staff. Would multiple textbooks allow me to filter the one listbox?

  9. #9
    istari88 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Kentucky, USA
    Posts
    13
    Concat field is auto generated in the table. Your search query I think would (F1,F2,F3) pull from the concat field. The trick is building the query.... The check box is flag when a row matches the search (somewhere within). Display rows flagged.
    Attached Thumbnails Attached Thumbnails FieldsSearchPic.JPG  

  10. #10
    istari88 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Kentucky, USA
    Posts
    13
    I like this as building the search string, after U get the bugs out!!!

    Quote Originally Posted by Ajax View Post
    it can be done, but depends how flexible it needs to be - how does the app know which fields to apply the criteria to. If it is just two, say StudentName and Level and always in the same order and you are not using lookup fields in your table design you could do something like


    Code:
    dim I as integer
    dim arr() as string
    arr=split(txtSearch,",")
    sqlStr=SELECT * FROM myTable WHERE StudentName='" & arr(0) & "'"
    if ubound(arr)>0 then sqlstr=sqlStr & " AND Level='" & arr(1) & "'"
    listbox.rowsource=sqlstr

  11. #11
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Created a calculated field in table. But completely lost on this one. Thanks for your input but don't want to keep asking. I will dig a little deeper and crack this.

  12. #12
    istari88 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Kentucky, USA
    Posts
    13
    that's the spirit!! Good luck!

  13. #13
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by istari88 View Post
    that's the spirit!! Good luck!
    Tried a few other options but no success. I thought this would be simple solution, but obviously not. Always open to further suggestions.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    per post #5, I can't fix something that I've provided and you have adapted unless you provide the code you have actually used, together with details of the outcome - err messages, wrong result etc

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you seen this example http://allenbrowne.com/ser-62.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Keyword Search Access 2010 Macros
    By waseem82 in forum Macros
    Replies: 1
    Last Post: 09-05-2016, 04:06 PM
  2. 2 Field Keyword Search
    By Emma35 in forum Forms
    Replies: 11
    Last Post: 01-19-2016, 08:34 AM
  3. KEYword search from a form field
    By Al77 in forum Forms
    Replies: 2
    Last Post: 03-30-2015, 12:43 PM
  4. keyword search
    By Mbakker71 in forum Access
    Replies: 5
    Last Post: 02-05-2014, 06:03 AM
  5. Multiple Keyword Search
    By gatsby in forum Access
    Replies: 15
    Last Post: 01-21-2013, 10:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums