combo box with multiple values for filtering by criteria

[June7]

Try changing spelling of SELCET to SELECT.

[Dachbo]

oh thanks a lot, pretty stupid mistake.

But now, I first get all the Employees with the "Y" entry, after the codes steps through the chck_OAP1 it only shows the Employees with OA_P1.
How do I get only the Employees with the "Y" AND OA_P1?

[June7]

Use as many criteria as you want in the WHERE clause.

You said user selects the field from combobox. I already showed you how to reference combobox.

"SELECT Employees_ID, Lastname, Firstname FROM tbl_EMPLOYEES WHERE " & Me.comboboxname & "='Y' AND [DEPTS] ='OA_P1';"

[Dachbo]

how do I do this with the if clause? I have more than one check box. It could be randomly selected. Maybe only one check box is true or several check boxes could be selected as true.

[Dachbo]

I found something and changed it a little bit.

The code steps through the first If Me.chck_OA and jumps to the BuildFilter but skips the If Len(... statement and jumps back to the If Me.chck_OAP1 and steps to the end of the code and then a msg box pops up and wants me to enter the value for Me.Filter

Whats wrong with the code, it is not filling the FilterDEPTS string

    Private Sub btn_filter_Click()
    Dim FilterDEPTS As String
    FilterDEPTS = ""
    
    If Me.chck_OA = -1 Then BuildFilter FilterDEPTS, "OA"
    If Me.chck_OAP1 = -1 Then BuildFilter FilterDEPTS, "OA_P1"
    
    
    Me.FilterOn = Len(FilterDEPTS) > 0
    Me.Filter = FilterDEPTS
    

    Me.lstbx_EMPLOYEES.RowSource = "SELECT EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE " & Me.cmbx_EMPLOYEES & "='Y' AND [DEPTS] = Me.Filter;"  

End Sub

Sub BuildFilter(ByRef FilterDEPTS As String, sAdd As String)
        If Len(FilterDEPTS) > 0 Then
            FilterDEPTS = FilterDEPTS & "AND"
            FilterDEPTS = FilterDEPTS & sAdd
        End If
            
End Sub

[June7]

FilterDEPTS is not a global declared variable. It is declared within a procedure so it only has a life within that procedure. Doesn't matter that you use the same variable name in two procedures. They have no connection, therefore every time each procedure is run, its variable starts out empty. If you want a variable to be available to multiple procedures, it must be declared in the module header.

Suggest you go back to the Allen Browne example and get that working before attempting to get more creative with code. For every control you want to contribute to the criteria string, build a conditional If Then.

[Dachbo]

I got the basic idea from Allen Browne.

How does it work to fill the strWhere string to look after multiple values for only one column? In the example he uses one value in the string for one column. I would like to use multiple values for only one column.

Something like this:
If OA, OA_P1, OA_P2... are checked, look in column DEPTS after OA or OA_P1 or OA_P2 and give me all records with either OA, OA_P1 and OA_P2....

How do I fill the strWhere string for this and how does the code look to find all records with either of those values?

[June7]

WHERE DEPTS="OA" OR DEPTS="OA_P1" OR DEPTS="OA_P2"

Or can use IN operator. Review http://allenbrowne.com/ser-50.html

WHERE DEPTS IN ("OA", "OA_P1", "OA_P2")

Again, build conditional If Then for each control.

[Dachbo]

he uses a list box to filter, but I have check boxes to filter.

I came up with an idea:

     Dim str_OA As String
    Dim str_OAP1 As String
    
    If Me.chck_OA = -1 Then
        str_OA = "OA"
    
    If Me.chck_OAP1 = -1 Then
         str_OAP1 = "OA_P1"

    Me.lstbx_EMPLOYEES.RowSource = "SELECT EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE " & Me.cmbx_EMPLOYEES & "='Y' AND [DEPTS] = 'str_OA' OR [DEPTS] = 'str_OAP1';"
    End If
    End If

it's not working, but could it work something like this?
I just recognized, it will skip everything it the first if statement is not true.... so I have to dump that idea

[June7]

Consider:

Dim strO As String
If Me.chck_OA = -1 Then
   strO = strO & "DEPTS = 'OA'"
End If
If Me.chck_OAP1 = -1 Then
   strO = strO & IIf(strO = "", "", " OR ") & "DEPTS = 'OA_P1'"
End If
strO = IIf(strO = "", "", "(" & strO & ")"
Me.lstbx_EMPLOYEES.RowSource = "SELECT EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE " & Me.cmbx_EMPLOYEES & "='Y'" & IIf(strOA = "", "", " AND ") & strO & ";"

[Dachbo]

thank you for the code,

unfortunately I get a compile error: "Expected: list separator or)" for this line:

strO = IIf(strO = "", "", "(" & strO & ")"

but I don't know how to fix it.

The other question is, how do I add a check box, for example when I have 4 or 5 check boxes and every one could be selected or not

[June7]

Sorry for typo. Just need closing paren for the IIf()

strO = IIf(strO = "", "", "(" & strO & ")")

If you have more checkboxes, are they for more DEPTS parameters? Just do more If Then structures as shown.

[Dachbo]

thank you!

What does this line exactly do?

[June7]

All it does is concatenate () to the string so the OR series criteria is enclosed in (). Correct parentheticals is critical when combining AND and OR operators in filter.

[Dachbo]

ok, thank you. It helped me to understand it and everything is working fine now!!