How to filter Rowsource Results Using Yes/No?

[James Bowyer]

I have a list box which I want to populate from a table that has lots of Yes/No values (Skills and Training, etc), and I want to be able to filter this box to show only the names of the people who have the right skills/qualifications for the job we need them on.

So, basically, I have set up the Form with a load of check boxes to provide the yes/No inputs. So far so good, however, how do I get it to ignore any check boxes I haven't checked, and only query using those I have?

[NeoPa]

Are you looking for records where any of the fields are checked or where all of them are?

[NeoPa]

Actually, the solutions are similar enough.

You don't provide any details so I will assume you have boolean fields called Q1, Q2, Q3, etc to reflect the various qualifications you refer to, and that you are looking to return some typical fields.

SELECT [GivenName]
     , [Initials]
     , [Surname]
FROM   [YourTable]
WHERE  [Q1]
   OR  [Q2]
   OR  [Q3]
   OR  ...

This SQL above selects the records where any of the qualification fields is TRUE.

SELECT [GivenName]
     , [Initials]
     , [Surname]
FROM   [YourTable]
WHERE  [Q1]
  AND  [Q2]
  AND  [Q3]
  AND  ...

This SQL above selects the records where all of the qualification fields are TRUE.

[James Bowyer]

Unfortunately the answer is both. For example I would want it to select the record where Q1 and Q3 are true, but ignore the rest, then the next time Q2, Q4 and Q7.. and so on.

Basically, so you can select those persons with the right skills for the job, which the OR function would do, but only for one skill, not multiples.

[NeoPa]

But you get the concepts right?

A boolean value doesn't need a comparison operator, as it is already boolean, which is what is returned by a comparison. [SomeField] >= 7 returns a boolean value of either TRUE or FALSE. A boolean variable is already boolean so doesn't need any such thing to be used where booleans are required (in a WHERE clause for instance).

Where you have multiple qualifications required then link them together with AND keywords. Where multiple sets of qualifications are possible alternatives then link any individual or sets of qualifications together using the OR keyword. When a set of qualifications is used as a possible alternative, it is important to encapsulate the set within parentheses ().

[James Bowyer]

In the end, I came up with this method:

Dim Driver As String
If DriverCheck.Value = False Then
Driver = ""
Else
Driver = "[Driver] = [Forms]![FitterSkillSelect]![DriverCheck] AND"
End If
 

For each of the checkboxes and then

SearchFitter.RowSource = "SELECT [FitterName] FROM [tblFittersLocal] WHERE  " & Driver & "[ID] > 0"
 

Obviously [ID]>0 is entirely pointless as all have an ID of more than 0, but it just stops there being an AND value stuck on the end.
And this worked well apart from the fact that it wouldn't work unless the checkbox had been checked and unchecked before running the code. So I did the following

Private Sub Form_Load()
 
[DriverCheck] = True
[DriverCheck] = False
End Sub
 

Any ideas why I would need to do this, or is this just one of access's little quirks?

[NeoPa]

It's hard to be sure James, as you don't indicate where most of your code runs, but let me guess :

Your first snippet of code, and similar ones for other controls, are found in AfterUpdate event procedures for the various controls. If the controls are never set then Driver could be undefined, and therefore result in invalid SQL being assigned to SearchFitter.RowSource. As I say, just a guess.

Assuming that each control is a CheckBox, and setting it means that the related boolean is required in the records we're looking for (and cleared means we can simply ignore), then I would do things the following way :

1. Start by setting the RowSource string to the simpler SELECT [FitterName] FROM [tblFittersLocal]. From here we work with a Filter string exclusively so the RowSource needd never be changed.
2. Ensure that the Filter string of the design matches the boolean settings set as default. If all are cleared then this would mean an empty Filter string of course, but if any are set then this should be reflected in the form's designed Filter string.
3. Each CheckBox's AfterUpdate event procedure should simply call a new procedure we will show below, called UpdateFilter().
4. Enter the following procedure, within the form's associated module, as :
   

   Private Sub UpdateFilter()
       Dim strFilter As String
    
       With Me
           If .DriverCheck Then _
               strFilter = strFilter & " AND ([Driver])"
           If .NextCheck Then _
               strFilter = strFilter & " AND ([Next])"
           'Repeat for all CeckBoxes cum boolean fields in the record
    
           With .SearchFitter
               .FilterOn = (strFilter > "")
               If .FilterOn Then .Filter = Mid(strFilter, 6)
           End With
       End With
   End Sub

I would suggest that, when you get this working, you have a look at the code and see if you can see why it does. I'm happy to answer any questions on it, but things like this come up all the time in form work, so it's well worth understanding.

[NeoPa]

Having just reread through to see if I could tie in some of the things I didn't understand, I now realise that SearchFitter is a ListBox control, and not another form. I should have realised when I was putting that together as it appeared to be related to Me (That was my best guess anyway). Now I reread the OP I can see it is indeed a ListBox control on the form. Give me a few minutes and I'll come up with a version of the code that is very similar, but directed towards a control on a form rather than on a form.

[NeoPa]

Right. A new list of instructions for handling a ListBox (or ComboBox even) :

Assuming that each control is a CheckBox, and setting it means that the related boolean is required in the records we're looking for (and cleared means we can simply ignore), then I would do things the following way :

1. Start by setting the RowSource string (in the design) to match the boolean settings set as default in the form. If all are cleared then this would mean the basic SELECT string, but if any are set then this should be reflected as a WHERE clause.
2. Each CheckBox's AfterUpdate event procedure should simply call a new procedure we will show below, called UpdateFilter().
3. Enter the following procedure, within the form's associated module, as :
   

   Private Sub UpdateFilter()
       Dim strSQL As String
    
       With Me
           If .DriverCheck Then strSQL = strSQL & " AND ([Driver])"
           If .NextCheck Then strSQL = strSQL & " AND ([Next])"
           'Repeat for all CheckBoxes / boolean field pairings in the record
    
           If strSQL > "" Then strSQL = " WHERE " & Mid(strSQL, 6)
           strSQL = "SELECT [FitterName] FROM [tblFittersLocal]" & strSQL
           With .SearchFitter
               .RowSource = strSQL
               Call .Requery
           End With
       End With
   End Sub

I would suggest that, when you get this working, you have a look at the code and see if you can see why it does. I'm happy to answer any questions on it, but things like this come up all the time in form work, so it's well worth understanding.