multi value field in where clause

  • Thread starter Thread starter KC_Cheer_Coach
  • Start date Start date
K

KC_Cheer_Coach

I have a field that is a list box and allows multiple values to be checked. I
am trying to select certain criteria based on one value within this field,
but Access keeps telling me I cannot use the multi value field in a WHERE
clause. Can you help?

tblJob.JobFunction is the multi value text field
qryPeople is the query that pulls the full name of the employee including
the job function. This returns all employees with multiple values in the job
function field.

I would like to find all male employees who have "analysis" listed as one of
their job functions.

SELECT qryPeople.FullName FROM qryPeople WHERE ((qryPeople.Sex="male") AND
(qryPeople.JobFunction="Analysis")) ORDER BY qryPeople.FullName;

If I remove the AND part of the statement, it returns all male employees. I
tried to use IN and LIKE but I couldn't get it to work.

Thanks in advance!
 
Nevermind. I new once I finally decided to post the issue, I would figure it
out. This works great:

SELECT qryPeople.FullName, tblJobFunction.JobFunction
FROM qryPeople INNER JOIN tblJobFunction ON qryPeople.JobFunction.Value =
tblJobFunction.JobFunction
WHERE (((qryPeople.Sex)="Male") AND ((tblJobFunction.JobFunction)="Analysis"))
ORDER BY qryPeople.FullName;
 
Back
Top