Query Filter

[Secret Squirrel]

I'm trying to test out a query filter that I need to build but I can't figure
out how to filter it properly. I'm using some test data with the following
structure:

tblTest

RecID - PK
EmpName - Text
Chk1 - Yes/No
Chk2 - Yes/No
Chk3 - Yes/No

Records:
RecID1

EmpName - John Doe
Chk1 - True
Chk2 - False
Chk3 - True

RecID2

EmpName - Jane Doe
Chk1 - True
Chk2 - True
Chk3 - True

Now on my form I have a listbox that I want to filter employee names with
using 3 unbound checkboxes on my form. The check boxes coincide with the 3
checks in my tblTest. The Listbox is using a qry from the tblTest to run as
my filter. I have all my fields in this query. I have the criteria for the 3
check fields in my query set to [Forms]![frmTest]![check1], etc.

So what I want to happen is when I check 1, 2, and 3 I only want to show
Jane Doe in the listbox since that emp. has a true value in the table for all
3 checks. When I check 1 and 3 and uncheck 2 I want to show Jane Doe and John
Doe in the listbox since Jane Doe has a true value in Checks 1 & 3 and John
Doe has a check in 1 & 3 also but I want to show both. I'm having a hard time
explaining this so I hope I was clear enough. Now I just need help figuring
out how to filter the query properly.

SS

 

[James A. Fortune]

I'm trying to test out a query filter that I need to build but I can't figure
out how to filter it properly. I'm using some test data with the following
structure:

tblTest

RecID - PK
EmpName - Text
Chk1 - Yes/No
Chk2 - Yes/No
Chk3 - Yes/No

Records:
RecID1

EmpName - John Doe
Chk1 - True
Chk2 - False
Chk3 - True

RecID2

EmpName - Jane Doe
Chk1 - True
Chk2 - True
Chk3 - True

Now on my form I have a listbox that I want to filter employee names with
using 3 unbound checkboxes on my form. The check boxes coincide with the 3
checks in my tblTest. The Listbox is using a qry from the tblTest to run as
my filter. I have all my fields in this query. I have the criteria for the 3
check fields in my query set to [Forms]![frmTest]![check1], etc.

So what I want to happen is when I check 1, 2, and 3 I only want to show
Jane Doe in the listbox since that emp. has a true value in the table for all
3 checks. When I check 1 and 3 and uncheck 2 I want to show Jane Doe and John
Doe in the listbox since Jane Doe has a true value in Checks 1 & 3 and John
Doe has a check in 1 & 3 also but I want to show both. I'm having a hard time
explaining this so I hope I was clear enough. Now I just need help figuring
out how to filter the query properly.

SS

The first thing I got to work for the Listbox's RowSource was:

SELECT tblTest.EmpName FROM tblTest WHERE
IIf(Forms!frmTest!check1.Value=-1,Check1 = -1,Check1=0 Or Check1=-1) AND
IIf(Forms!frmTest!check2.Value=-1,Check2 = -1,Check2=0 Or Check2=-1) AND
IIf(Forms!frmTest!check3.Value=-1,Check3 = -1,Check3=0 Or Check3=-1);

Along with the table and data you specified, I used the following code
behind a form called frmTest:

Option Compare Database
Option Explicit

Private Sub check1_Click()
theListbox.Requery
End Sub

Private Sub check2_Click()
theListbox.Requery
End Sub

Private Sub check3_Click()
theListbox.Requery
End Sub

A more clever query might look like:

SELECT tblTest.EmpName FROM tblTest WHERE (Check1 = -1 Or Check1 =
Forms!frmTest!check1.Value) AND (Check2 = -1 Or Check2 =
Forms!frmTest!check2.Value) AND (Check3 = -1 Or Check3 =
Forms!frmTest!check3.Value);

There might even be simpler ways. Post back if you'd like me to give it
some more thought.

Note: I used 0 for the default value of the checkboxes.

James A. Fortune
(e-mail address removed)

 

[Secret Squirrel]

Hi James,

Thank you for the ideas. I kinda like your first idea with the "IIF"
statements. Your second idea is pretty much the same, correct? I think either
one of these would work fine. I tested it out and it gives me exactly what
I'm looking for. One question though. When I open the form and all the check
boxes are defaulted to "0", why do all the employees show up in the list
already? Shouldn't there be no employees in the list to start?

SS

I'm trying to test out a query filter that I need to build but I can't figure
out how to filter it properly. I'm using some test data with the following
structure:

tblTest

RecID - PK
EmpName - Text
Chk1 - Yes/No
Chk2 - Yes/No
Chk3 - Yes/No

Records:
RecID1

EmpName - John Doe
Chk1 - True
Chk2 - False
Chk3 - True

RecID2

EmpName - Jane Doe
Chk1 - True
Chk2 - True
Chk3 - True

Now on my form I have a listbox that I want to filter employee names with
using 3 unbound checkboxes on my form. The check boxes coincide with the 3
checks in my tblTest. The Listbox is using a qry from the tblTest to run as
my filter. I have all my fields in this query. I have the criteria for the 3
check fields in my query set to [Forms]![frmTest]![check1], etc.

So what I want to happen is when I check 1, 2, and 3 I only want to show
Jane Doe in the listbox since that emp. has a true value in the table for all
3 checks. When I check 1 and 3 and uncheck 2 I want to show Jane Doe and John
Doe in the listbox since Jane Doe has a true value in Checks 1 & 3 and John
Doe has a check in 1 & 3 also but I want to show both. I'm having a hard time
explaining this so I hope I was clear enough. Now I just need help figuring
out how to filter the query properly.

SS

The first thing I got to work for the Listbox's RowSource was:

SELECT tblTest.EmpName FROM tblTest WHERE
IIf(Forms!frmTest!check1.Value=-1,Check1 = -1,Check1=0 Or Check1=-1) AND
IIf(Forms!frmTest!check2.Value=-1,Check2 = -1,Check2=0 Or Check2=-1) AND
IIf(Forms!frmTest!check3.Value=-1,Check3 = -1,Check3=0 Or Check3=-1);

Along with the table and data you specified, I used the following code
behind a form called frmTest:

Option Compare Database
Option Explicit

Private Sub check1_Click()
theListbox.Requery
End Sub

Private Sub check2_Click()
theListbox.Requery
End Sub

Private Sub check3_Click()
theListbox.Requery
End Sub

A more clever query might look like:

SELECT tblTest.EmpName FROM tblTest WHERE (Check1 = -1 Or Check1 =
Forms!frmTest!check1.Value) AND (Check2 = -1 Or Check2 =
Forms!frmTest!check2.Value) AND (Check3 = -1 Or Check3 =
Forms!frmTest!check3.Value);

There might even be simpler ways. Post back if you'd like me to give it
some more thought.

Note: I used 0 for the default value of the checkboxes.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Hi James,

Thank you for the ideas. I kinda like your first idea with the "IIF"
statements. Your second idea is pretty much the same, correct?

They should work exactly the same. Actually, I don't think I've ever
tried what I did with the IIF version before. It might open up some new
possibilities.

I think either
one of these would work fine. I tested it out and it gives me exactly what
I'm looking for. One question though. When I open the form and all the check
boxes are defaulted to "0", why do all the employees show up in the list
already? Shouldn't there be no employees in the list to start?

I think all the employees showing up in the list to start is the correct
behavior. With no check boxes checked, you're not applying any kind of
filtering so everyone should show up. Obviously, it's easy to change
the behavior so that both checked and unchecked values provide
filtering, but that would not give you the results you requested in your
original post.

James A. Fortune
(e-mail address removed)

 

[Secret Squirrel]

Thanks for the clarification. I'm good to go now. Thank you again for your
help! I appreciate it!

SS

 

[Secret Squirrel]

Hi James,

Thank you for helping me out with this last month but I've run into a bit of
a problem. As you recall I was trying to filter my listbox based on
checkboxes on my form. I have a total of 135 checkboxes on my form so my
selection criteria is very large. Well I found out that the most AND
statements you can have in a query is 99. So either I need to lower the
amount of checkboxes or find another solution. Would you happen to have any
ideas on how I can do this and keep the 135 checkboxes? Someone had mentioned
using a dynamic query but I have to be honest, I don't know much about them.
Any help would be greatly appreciated.

SS

I'm trying to test out a query filter that I need to build but I can't figure
out how to filter it properly. I'm using some test data with the following
structure:

tblTest

RecID - PK
EmpName - Text
Chk1 - Yes/No
Chk2 - Yes/No
Chk3 - Yes/No

Records:
RecID1

EmpName - John Doe
Chk1 - True
Chk2 - False
Chk3 - True

RecID2

EmpName - Jane Doe
Chk1 - True
Chk2 - True
Chk3 - True

Now on my form I have a listbox that I want to filter employee names with
using 3 unbound checkboxes on my form. The check boxes coincide with the 3
checks in my tblTest. The Listbox is using a qry from the tblTest to run as
my filter. I have all my fields in this query. I have the criteria for the 3
check fields in my query set to [Forms]![frmTest]![check1], etc.

So what I want to happen is when I check 1, 2, and 3 I only want to show
Jane Doe in the listbox since that emp. has a true value in the table for all
3 checks. When I check 1 and 3 and uncheck 2 I want to show Jane Doe and John
Doe in the listbox since Jane Doe has a true value in Checks 1 & 3 and John
Doe has a check in 1 & 3 also but I want to show both. I'm having a hard time
explaining this so I hope I was clear enough. Now I just need help figuring
out how to filter the query properly.

SS

The first thing I got to work for the Listbox's RowSource was:

SELECT tblTest.EmpName FROM tblTest WHERE
IIf(Forms!frmTest!check1.Value=-1,Check1 = -1,Check1=0 Or Check1=-1) AND
IIf(Forms!frmTest!check2.Value=-1,Check2 = -1,Check2=0 Or Check2=-1) AND
IIf(Forms!frmTest!check3.Value=-1,Check3 = -1,Check3=0 Or Check3=-1);

Along with the table and data you specified, I used the following code
behind a form called frmTest:

Option Compare Database
Option Explicit

Private Sub check1_Click()
theListbox.Requery
End Sub

Private Sub check2_Click()
theListbox.Requery
End Sub

Private Sub check3_Click()
theListbox.Requery
End Sub

A more clever query might look like:

SELECT tblTest.EmpName FROM tblTest WHERE (Check1 = -1 Or Check1 =
Forms!frmTest!check1.Value) AND (Check2 = -1 Or Check2 =
Forms!frmTest!check2.Value) AND (Check3 = -1 Or Check3 =
Forms!frmTest!check3.Value);

There might even be simpler ways. Post back if you'd like me to give it
some more thought.

Note: I used 0 for the default value of the checkboxes.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Hi James,

Thank you for helping me out with this last month but I've run into a bit of
a problem. As you recall I was trying to filter my listbox based on
checkboxes on my form. I have a total of 135 checkboxes on my form so my
selection criteria is very large. Well I found out that the most AND
statements you can have in a query is 99. So either I need to lower the
amount of checkboxes or find another solution. Would you happen to have any
ideas on how I can do this and keep the 135 checkboxes? Someone had mentioned
using a dynamic query but I have to be honest, I don't know much about them.
Any help would be greatly appreciated.

SS

Off the top of my head, the easiest thing I can suggest is to note that
there is probably no limit to the number of multiplication operators.
If any of the boolean value terms multiplied together are false, then
the entire expression should evaluate to 0. Something like (air code):

WHERE (Forms!frmLotsOfChecks!chk1.Value = 0) *
(Forms!frmLotsOfChecks!chk2.Value = 0) *
(Forms!frmLotsOfChecks!chk3.Value = 0) <> 0

That should only return records if at least one of the three checkboxes
is checked. That example should give you an idea of what can be used in
your situation. I have a form that I use for flexible reporting with a
very large number of checkboxes also, but I only allow the user to check
up to 10 fields to appear in the flexible report.

James A. Fortune
(e-mail address removed)