combo box listing all employees or only ones in certain departments

[cbrxxrider]

I have two combo boxes. one lists all departments and one is bound to the department combo box. So when any certain department is chosen, it only shows the employees within that department.

My employee combo box row source is -



SELECT Employee.EmployeeID, Employee.[First Name], Employee.[Last Name] FROM Department INNER JOIN Employee ON Department.Department = Employee.Department WHERE (((Employee.[Manager/Supervisor])=No) And ((Department.Department)=Forms!CompletedGoalForm!D epartment)) Or (((Department.Department)="all")) ORDER BY Employee.[First Name];

I'm pretty sure I'm just missing something small here but I need to put an OR All or something in here somewhere so that when the department combo is left alone and no department is chosen, all the employees will be listed in the employee combo box.

[June7]

Use LIKE operator and wildcard:

SELECT Employee.EmployeeID, Employee.[First Name], Employee.[Last Name] FROM Department INNER JOIN Employee ON Department.Department = Employee.Department WHERE Employee.[Manager/Supervisor]=No And Department.Department LIKE Forms!CompletedGoalForm!Department & "*" ORDER BY Employee.[First Name];