Multi-value Text Field Search

[billfold]

First, I wanted to say hi, since this is my first post.

Here is my issue. I am using just one table for this query, so it should be pretty straight forward, but I can't seem to return the results that I want. The query is based on Employee ID's and what departments they have associated with them. However, whoever designed the database made it a text field that could hold multiple values, and each value is comma delimited. Say I want to find all employee id's that are either associated to each department, or if an employee has access to multiple departments.

So employee ID's 1, 2, 3
Departments: a,b,c

EmpID:1
Departments: a

EmpID: 2
Departments: a,b

EmpID: 3
Departments: a,b,c,d,e,f

Here is my most recent query. I know this isn't the answer, but I am at a loss here.

SELECT
EmpID, Departments
FROM
User
WHERE Departments IN ('a','b','c')



Thank you

Bill

[June7]

You need to determine if the field is a true multi-value (review https://www.accessforums.net/showthr...cus-on-Subform) or just a comma separated string in a text field.

[billfold]

It's just a comma seperated text field, not a true multivalue field.

[June7]

Then the value is a single continuous string of 'a, b, c', not separate values of 'a', 'b', 'c'. This isn't going to work.

You could use LIKE operator for cases such as: 'a, b, c, d' LIKE '*a, b*' or InStr("a, b, c, d", "a, b")

But 'a, b, c' will not match against '*a, c*' and InStr("a, b, c", "a, c") will return 0.

Will need VBA function code to parse and compare the string parts then return a True or False value to base criteria on.