Select Access Items where Checkboxes indicate Field Values

Question

In my database I have a table with several yes/no columns.

Now, I have a unbound form with some unbound checkboxes and a listbox bound to a special query just for it.

I want to use the checkboxes on the form to filter the listbox and display items in the list that have matching values.

For example,

If I check box1 on the form, any item that contains field1 in the listbox should show.
If I check box2 and box3 on the form, any item that contains field2 AND field3 should display in the listbox. If an item does not have both field3 AND field3, their entries should not show in the list because the boxes are not checked.

Each control on the form uses an on-click event procedure to cause the list to requery.

Right now, this doesn't work right for me. My listbox only displays items when ALL the values match the checkbox. So, if I check box1 and box2, I only get the results from items that have only field1 and field2. If the item also has field3, it doesn't display - and this is my problem.

This is what my query looks like so far:

SELECT MyQuery.title, field1, MyQuery.field2, MyQuery.field3
FROM MyQuery
WHERE (
(
(MyQuery.field1) like [Forms]![Ability Finder]![box1]
)AND(
(MyQuery.field2) like [Forms]![Ability Finder]![box2]
)AND(
(MyQuery.field2) like [Forms]![Ability Finder]![box3]
)

);

Maybe I'm going about this all the wrong way. Also, I have about 20 checkboxes for which I need to do this.

SELECT MyQuery.cat, MyQuery.nickname, MyQuery.title, MyQuery.level, MyQuery.field1, MyQuery.field2, MyQuery.field3
FROM MyQuery
WHERE (
((MyQuery.field1)=[Forms]![Ability Finder]![box1] Or [Forms]![Ability Finder]![box1] Is Null) 
AND ((MyQuery.field2)=[Forms]![Ability Finder]![box2] Or [Forms]![Ability Finder]![box2] Is Null) 
AND ((MyQuery.field3)=[Forms]![Ability Finder]![box3] Or [Forms]![Ability Finder]![box3] Is Null)
);

From what I understand Where clause should by dynamic for example if [Forms]![Ability Finder]![box1] has a a value (selected) then following line should be included: MyQuery.field1) like [Forms]![Ability Finder]![box1] Otherwise this line should be omitted . — BData, Jun 19 '18 at 02:48
You need to add something like `[Forms]![Ability Finder]![box3] OR [Forms]![Ability Finder]![box3]is Null ` To get the unused query criteria to be ignored. — Minty, Jun 19 '18 at 08:21
But won't the checkboxes be null only if I've never clicked in them? If I toggle one on and off, then it's null state is lost. But adding the `is null` part doesn't help anything. — bgmCoder, Jun 19 '18 at 14:52
@BData You are correct that the clause should be dynamic, which is why I need to consult the value of box1, etc. But you are saying I should ignore the box if it isn't checked. Well, it already does do that. If I uncheck a box, then it isn't filtered on it. My problem is the other direction. — bgmCoder, Jun 19 '18 at 14:55

score 1 · Accepted Answer · answered Jun 19 '18 at 16:36

Not sure if you want to take a crack at writing the event handling in VBA, but it will make this task a lot easier. You can construct the SQL query dynamically each time a checkbox is clicked, and it can handle any additional checkboxes if you add them down the line simply by adjusting the For loop. You call the Sql constructor function for the Click event of each checkbox, then set the listbox's row source in the constructor function.

Private Sub Check1_Click()

ConstructSqlQuery

End Sub

Private Sub Check2_Click()

ConstructSqlQuery

End Sub

Private Sub Check3_Click()

ConstructSqlQuery

End Sub

...

Private Sub ConstructSqlQuery()

Dim sql As String
Dim numChecked As Integer
Dim checkboxName As String
Dim criteriaBoxName As String

numChecked = 0

'the 1=1 is a dummy value that always returns true. It makes it easier to append additional "and" clauses.
sql = "select field1, field2, field3, ... from MyQuery where 1=1"

For x = 1 To 15
    checkboxName = "Check" & x
    criteriaBoxName = "Text" & x
    If Me.Controls(checkboxName).Value = -1 Then
        sql = sql & " and field" & x & " like '*" & Me.Controls(criteriaBoxName).Value & "*'"
        numChecked = numChecked + 1
    End If
Next

If numChecked = 0 Then
    'if nothing is checked, don't show anything.
    Me.List0.RowSource = ""
Else
    Me.List0.RowSource = sql
End If

Me.List0.Requery

End Sub

I don't mind doing this in VBA! I was wondering if I'd end up doing it that way. Thank you for the example - I'll try it out. — bgmCoder, Jun 19 '18 at 18:31
This idea worked! I used a control loop from here though: https://stackoverflow.com/questions/3344649/how-to-loop-through-all-controls-in-a-form-including-controls-in-a-subform-ac so I can also loop through text boxes. But the idea of using the sql query to load the rowsource is what did it. — bgmCoder, Jun 19 '18 at 19:57
@bgmCoder, very cool! Glad it worked. That other loop looks more graceful because it handles all the control types on the form; that's a handy link. — James Toomey, Jun 19 '18 at 21:46

Select Access Items where Checkboxes indicate Field Values

1 Answers1

Linked