And/Or Option Group for Multiselect List Box

[Guest]

Hello,

I'm not sure if this is possible, but I would like to create a multiselect
list box that has option group controls of 'And' and 'Or' that will allow the
user to search either all of the selections from the list box or any one of
the selections.

My table is tblSkills and the field name is Skill. I want the list box to
populate a list of skills and allow the user to find an employee with either
all of the skills selected or any one of the skills selected.

Thanks so much for your time and effort!

 

[Douglas J. Steele]

If I'm understanding your question correctly, this has nothing to do with
And vs Or.

When dealing with a multiselect list box, you have no choice but to
dynamically regenerate the SQL, using code such as is presented in
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web". If the
user indicates that they want to select all values in the list box, simply
ignore that field in your Where clause.

 

[Guest]

The 'And' option I would want to create would retrieve the names of
employees who had all of the skills *selected* in the multiselect listbox (as
opposed to retrieving everything in the list box) The 'Or' option I would
want to create would retrieve the names of employees who had any of the
skills *selected* in the multiselect listbox. How could I make this happen?

Thanks so much for your help and time.

 

[Douglas J. Steele]

To know those employees with all of the skills, you can use

SELECT EmployeeNm
FROM MyTable
WHERE Skill IN (1, 2, 3)
GROUP BY EmployeeNm
HAVING Count(*) = 3

(where 3 is the number of skills being searched for)

To know those employees with any of the skills, you can use

SELECT EmployeeNm
FROM MyTable
WHERE Skill IN (1, 2, 3)
GROUP BY EmployeeNm
HAVING Count(*) > 0

 

[Guest]

Thanks so much Douglas--you saved the day!!

To know those employees with all of the skills, you can use

SELECT EmployeeNm
FROM MyTable
WHERE Skill IN (1, 2, 3)
GROUP BY EmployeeNm
HAVING Count(*) = 3

(where 3 is the number of skills being searched for)

To know those employees with any of the skills, you can use

SELECT EmployeeNm
FROM MyTable
WHERE Skill IN (1, 2, 3)
GROUP BY EmployeeNm
HAVING Count(*) > 0