' This code uses DAO and is suitable for Access 97. If using this code in
' Access 2000 (and later) a reference must be set to the Microsoft DAO 3.x
' Object Library (where x is the highest available number).

Private Sub cmdOK_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim blnQueryExists As Boolean
    Dim varItem As Variant
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strDepartmentCondition As String
    Dim strGenderCondition As String
    Dim strSQL As String
' Check for the existence of the stored query
    Set db = CurrentDb
    blnQueryExists = False
    For Each qdf In db.QueryDefs
        If qdf.Name = "qryStaffListQuery" Then
            blnQueryExists = True
            Exit For
        End If
    Next qdf
' Create the query if it does not already exist
    If blnQueryExists = False Then
        Set qdf = db.CreateQueryDef("qryStaffListQuery")
    End If
    Application.RefreshDatabaseWindow
' Turn off screen updating
    DoCmd.Echo False
' Close the query if it is already open
    If SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryStaffListQuery"
    End If
' Build criteria string for Office
    For Each varItem In Me.lstOffice.ItemsSelected
        strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) & "'"
    Next varItem
    If Len(strOffice) = 0 Then
        strOffice = "Like '*'"
    Else
        strOffice = Right(strOffice, Len(strOffice) - 1)
        strOffice = "IN(" & strOffice & ")"
    End If
' Build criteria string for Department
    For Each varItem In Me.lstDepartment.ItemsSelected
        strDepartment = strDepartment & ",'" & Me.lstDepartment.ItemData(varItem) & "'"
    Next varItem
    If Len(strDepartment) = 0 Then
        strDepartment = "Like '*'"
    Else
        strDepartment = Right(strDepartment, Len(strDepartment) - 1)
        strDepartment = "IN(" & strDepartment & ")"
    End If
' Build criteria string for Gender
    For Each varItem In Me.lstGender.ItemsSelected
        strGender = strGender & ",'" & Me.lstGender.ItemData(varItem) & "'"
    Next varItem
    If Len(strGender) = 0 Then
        strGender = "Like '*'"
    Else
        strGender = Right(strGender, Len(strGender) - 1)
        strGender = "IN(" & strGender & ")"
    End If
' Get Department condition
    If Me.optAndDepartment.Value = True Then
        strDepartmentCondition = " AND "
    Else
        strDepartmentCondition = " OR "
    End If
' Get Gender condition
    If Me.optAndGender.Value = True Then
        strGenderCondition = " AND "
    Else
        strGenderCondition = " OR "
    End If
' Build SQL statement
    strSQL = "SELECT tblStaff.* FROM tblStaff " & _
             "WHERE tblStaff.[Office] " & strOffice & _
             strDepartmentCondition & "tblStaff.[Department] " & strDepartment & _
             strGenderCondition & "tblStaff.[Gender] " & strGender & ";"
' Apply the SQL statement to the stored query
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryStaffListQuery")
    qdf.SQL = strSQL
    Set qdf = Nothing
    Set db = Nothing
' Open the Query
    DoCmd.OpenQuery "qryStaffListQuery"
' If required the dialog can be closed at this point
 '    DoCmd.Close acForm, Me.Name
' Restore screen updating
    DoCmd.Echo True
End Sub

Private Sub optAndDepartment_Click()
' Toggle option buttons
    If Me.optAndDepartment.Value = True Then
        Me.optOrDepartment.Value = False
    Else
        Me.optOrDepartment.Value = True
    End If
End Sub

Private Sub optAndGender_Click()
' Toggle option buttons
    If Me.optAndGender.Value = True Then
        Me.optOrGender.Value = False
    Else
        Me.optOrGender.Value = True
    End If
End Sub

Private Sub optOrDepartment_Click()
' Toggle option buttons
    If Me.optOrDepartment.Value = True Then
        Me.optAndDepartment.Value = False
    Else
       Me.optAndDepartment.Value = True
    End If
End Sub

Private Sub optOrGender_Click()
' Toggle option buttons
    If Me.optOrGender.Value = True Then
        Me.optAndGender.Value = False
    Else
        Me.optAndGender.Value = True
    End If
End Sub