Results 1 to 7 of 7
  1. #1
    Newbie7777 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    4

    Complex problem: How to filter a concatenated field to a MULTI-SELECT LIST BOX?

    Hello there,

    I've searched on the web to no avail and also posted on Microsoft support forum without any luck.



    I need to filter a concatenated field (ex: Maine, Vermont, etcc) to a MULTI-SELECT LIST BOX (i.e. a list of States) in the search form I created
    using Allen Browne's Search Form tips code.

    Note: Previously I spent hours using the cumbersome queries method (ex: forms!FormName!controloject) where you have to
    come up with all the combinations of a search for all the fields you are searching. In this method I WAS ABLE to successfully filter
    a concatenated field to a MULTI-SELECT field thanks to help from a microsoft support person (using a Match function). Unfortunately trying to
    come up with all the combinations became to complex as I had too many search criterias. Hence I decided to try Allen Browne's tips.

    Here's the link to the db, which is easier than explaining the problem. Basically my search results has a field called States --which contains
    the concatenated values. I want to filter this field to the multi-select list box "States".

    Thanks in advance to anyone who can help! This seems to be almost impossible to do as I can not find any advice on it.

    http://dl.dropbox.com/u/78512764/MS%...FormFilter.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I expect the function could be modified to build a filter string that would include criteria for the concatenated state field that uses LIKE and OR operators (or InStr function and OR operator). Concern is if there is a limit to how long the filter string can be. Review http://office.microsoft.com/en-us/ac...010341462.aspx

    Another approach is to have a yes/no field called Retrieve in MainTable. Then use code to set the field to Yes if any of the given criteria are met. This could mean using Recordset, cycyling through recordset and testing each record if data meets selected criteria. Then form would have criteria to filter on the Retrieve field. Could run slowly.

    Query1 is not returning all records from MainTable because of query jointype. Is that what you want?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Newbie7777 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    I expect the function could be modified to build a filter string that would include criteria for the concatenated state field that uses LIKE and OR operators (or InStr function and OR operator). Concern is if there is a limit to how long the filter string can be. Review http://office.microsoft.com/en-us/ac...010341462.aspx

    Another approach is to have a yes/no field called Retrieve in MainTable. Then use code to set the field to Yes if any of the given criteria are met. This could mean using Recordset, cycyling through recordset and testing each record if data meets selected criteria. Then form would have criteria to filter on the Retrieve field. Could run slowly.

    Query1 is not returning all records from MainTable because of query jointype. Is that what you want?

    Thank you for the help! Unfortunately I'm afraid I am too much of a newbie to really understand what you are advising me to do, the terminology is
    new to me. If you could provide a bit more simpler steps, I think I will maybe be able to follow? Forgive my newbie ignorance
    Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you doing this on a form anyway? These records are not editable.

    What specifically do you not understand? The potential limitation on filter string length? How to modify Browne's function? How to use Yes/No field to tag records for filtering? How to write code to set the Yes/No field? How to modify Query1 to show all records from MainTable?

    I did get this to work.
    Code:
       ' Build criteria string from selected items in list box.
        If Me.cboStates.ItemsSelected.Count > 0 Then
            If strWhere <> "" Then strWhere = strWhere & "("
            For Each i In Me.cboStates.ItemsSelected
                If Criteria <> "" Then
                   Criteria = Criteria & " OR "
                End If
                Criteria = Criteria & "Statess LIKE '*" & Me.cboStates.ItemData(i) & "*'"
            Next i
        strWhere = strWhere & Criteria & IIf(InStr(strWhere, " AND ") > 0, ")", "") & " AND "
        End If
    I removed the two lines following the listbox code that apply the Criteria string to filter.
    Last edited by June7; 08-13-2012 at 10:14 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Newbie7777 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    4
    Hi there,
    Sorry for the delay in responding. I was out at training all week.
    Thank you sooo much for your kind help!

    Let me answer your questions:

    Q: Why are you doing this on a form anyway? These records are not editable.
    A: Actually, the records I am displaying in the subform will ultimately have a control button
    that will allow the users to go to a form (that I have not yet created) to make edits to the specific record. This key reason for this search form is to allow the user
    to narrow down the search to find the specific training record they need to edit.

    Q:What specifically do you not understand?
    A: I don't know how to adapt Allen Browne's VBA code (that addresses how to filter to a multi-select listbox) to a concatenated field.
    As you can see, I was able to adapt Allen Browne's code to make the other filters work (i.e. filter to a text box, filter to a combo box, filter to a Yes/No box)
    with sucess. I'm just really stuck on this last problem of filtering a concatenated field to a mult-select box. I'm beginning to think that it's not possible...although
    I have not yet tried the code you suggested.

    I will try the code you so kindly offered and will let you know how I make out.
    Thank you again for your help! It seems that this is more advanced a problem that most people can handle since all the forums I've tried have not been
    able to offer any suggestions.

    Regards,

  6. #6
    Newbie7777 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    4
    HOLD THE PRESSES!! You are AWESOME!!
    I just tried the code you provided and IT WORKS LIKE A CHARM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Seriously, I've asked so many advanced MS Access people about this problem and no one has been able to figure it out!

    THANK YOU, THANK YOU, THANK YOU SOOOOOOOOOOOOOOOOOO MUCH!!!
    It's been 3 weeks of constantly searching the web for help!

    Honestly, thank you SOOOOOO MUCH!!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Glad you are satisfied with the solution. It was a rather unique challenge for me as well. Getting the syntax right for the VBA construction of the OR series with LIKE and wildcard took a while. Handling the parenthesis was a fix I don't fully understand. If parenthesis included without other criteria, code mysteriously bombs, hence the condition that looks for AND operator in the string.

    I hope you don't encounter a string length limit for the Filter property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. multi select list box
    By crowegreg in forum Forms
    Replies: 1
    Last Post: 07-28-2012, 11:52 PM
  2. Replies: 5
    Last Post: 07-26-2012, 12:59 PM
  3. Replies: 2
    Last Post: 03-27-2012, 01:02 PM
  4. Replies: 1
    Last Post: 10-22-2010, 10:11 AM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 AM

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