Full Version: Filter Form With Multiple Items In A Field
UtterAccess Forums > Microsoft® Access > Access Forms
niloufar
On My query I have a field with multiple Names. Some are Managers and some are Auditors.

On my form, I added a field with two Items , Managers and Auditors. So, when I select Managers I would like to filter my subform and query with 5 names in my record and if I select Managers, I would like to be able filter my form/query with 3 names in my record.
below is my VBA code, but it's not working,

CODE
Private Sub cboAuditor_AfterUpdate()

    strManagers = "<>'Steven' And <>'Lisa' And <>'Christopher' And <>'Ronda' "
    strAuditors = "'Steven' Or 'Lisa' Or 'Christopher' Or 'Ronda' Or 'Amber'"


        If Me.cboAuditor = "Managers" Then
            strAnalystFilter = "[Created By] = '" & strManagers & "'"

        ElseIf Me.cboAuditor = "Auditors" Then            
           strAnalystFilter = "[Created By] = '" & strAuditors & "'"

        End If

    End Sub
azolder
Hard coding the names is not good practice. I'd recommend an In Clause. Using conceptual code:
CODE
[CreatedBy] In (SELECT name FROM tbEmployees WHERE Position={either Auditor or Manager})


EDIT:
If you insist on the hard coded names, you could just use one string
CODE
        strAuditors = "'Steven',  'Lisa',  'Christopher', 'Ronda', 'Amber'"
        If Me.cboAuditor = "Managers" Then
            strAnalystFilter = "[Created By] Not In (" & strAuditors ")"

        ElseIf Me.cboAuditor = "Auditors" Then            
           strAnalystFilter = "[Created By]  In (" & strAuditors ")"

        End If
niloufar
Long story short, I have to hard code it. But still not working and I get error now.

the error is :The expression is typed incorrectly or is too complex.
azolder
Verify you've strAuditors is typed correctly, and that you're concatenating strAuditors in to your criteria.

Does the query work when you aren't using the criteria?

Also using the expression as a parameter in you're query will not work. You'd have to build the SQL query as a sting in VBA, or use the expression as a form and/or report filter.

EDIT:
If you want to open the query as a datasheet you'd need to do it using VBA:
CODE
         DoCmd.OpenQuery strQueryName
         If Len(strAnalystFilter) > 0 Then _
            DoCmd.ApplyFilter WhereCondition:=strAnalystFilter
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.