Results 1 to 5 of 5
  1. #1
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20

    query based on multiple checkboxes

    My table has 6 yes/no fields. Let's call them A, B, C, D, E, F.

    I can design a simple query that returns all the entries based on a single yes/no value for any one of the 6 datatypes (A alone, B alone, etc.). I can design a query that has a the criteria either/or or and (A and B, A or B, A and B and C, etc.). I can envision 720 different permutations!



    I have a general query of the table data in a split form, and at the top of the split form, I have each checkbox with an individual ApplyFilter:
    Code:
    [Alike "*" & [Forms]![frm_data]![chkA] & "*" 
    This does a fine job of showing all the data where the Field "A" is true.

    What I'd really like to do is be able to check multiple checkboxes and have the filter return the true values for all those checkboxes that are checked. In other words, If I check off A, B, D, F, I get the data filtered in the split form where A, B, D, F are all true.

    Where do I start?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Doable, but it would take some code. Let's back up though. Your table does not sound normalized. What are you going to do when there's a 7th option? Normally we would have a related table, with a record for each of those options. What do the options represent, relative to the rest of the table? More here:

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20
    Thanks for the reference.

    You're probably correct in that my table is not as normalized as it could be. The initial table is pretty basic. It has: ID, FirstName, LastName, DateofBirth, AllergicRhinitis, Asthma, Smoker, Diabetes, Bronchiectasis, Eosinophilia. Those last 6 are conditions. You either have them or you don't. Yes/no. I do have some related tables at all link based on the ID, but they are -- I believe -- irrelevant to this mission.

    As much as it might benefit the cause to redesign the database and achieve a higher degree of normalization, I don't have the motivation to redesign it at this point since I simply cannot invest any more time at this point (perhaps later). I realize that may be my undoing later or with this search effort.

    As for the 7th option, I could envision adding one later -- perhaps that's why my tables should be more normalized.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Code:
    Public Function fcnBuildQuery() As String
        Dim sCrit As String
        Dim nCtr As Long
        Dim ChkA As Boolean, ChkB As Boolean, ChkC As Boolean, ChkD As Boolean, ChkE As Boolean, ChkF As Boolean
        Dim collectChks As Collection
        ChkC = True
        ChkF = True
        sCrit = "WHERE "
        Set collectChks = New Collection
        If ChkA = True Then collectChks.Add "ChkA"
        If ChkB = True Then collectChks.Add "ChkB"
        If ChkC = True Then collectChks.Add "ChkC"
        If ChkD = True Then collectChks.Add "ChkD"
        If ChkE = True Then collectChks.Add "ChkE"
        If ChkF = True Then collectChks.Add "ChkF"
        If collectChks.Count = 0 Then
            ' no checkboxes checked
            Exit Function
        End If
        nCtr = collectChks.Count
        Select Case nCtr
            Case 1
                sCrit = sCrit & collectChks.Item(1) & " = True "
            Case Else
                While nCtr > 0
                    sCrit = sCrit & collectChks.Item(nCtr) & " = True OR "
                    nCtr = nCtr - 1
                Wend
        End Select
        if collectChks.count > 1 then sCrit = Left(sCrit, Len(sCrit) - 4)  ' strip off the OR
        fcnBuildQuery = sCrit
        Debug.Print sCrit
    End Function
    Here's a fairly simple way to build the criteria. It uses a VBA collection.
    The collection will contain the names of the checkboxes that have a value of TRUE
    For simplicity I've substituted variables for the checkboxes and given a couple of the variables TRUE value.
    This doesn't have to be a function, I just used that to contain the code and get it to work.
    If you want specific combinations of checkboxes, you could expand the Select Case to handle that.
    Last edited by davegri; 11-27-2016 at 12:05 AM. Reason: bug fix

  5. #5
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20
    Wow, thanks! I will give this a whirl.

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

Similar Threads

  1. Replies: 9
    Last Post: 04-26-2014, 07:10 PM
  2. Replies: 2
    Last Post: 08-21-2013, 09:39 AM
  3. Replies: 1
    Last Post: 06-03-2013, 08:57 AM
  4. Filter Query based upon Checkboxes
    By olinms01 in forum Queries
    Replies: 2
    Last Post: 01-21-2013, 11:38 AM
  5. Filter records based on multiple checkboxes
    By kbremner in forum Forms
    Replies: 2
    Last Post: 01-18-2011, 10:59 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