Replacing commas in Multi-Value Lists into Or Conditions in queries

[jdashm]

I have a multi-value list in a table which allows for users to select multiple departments.




I want to be able to use the multiple values in a query as "Or" Operators.
So far I have created a Replace function that replaces the commas with the word "or"


Replace function...
Dim LResult1 As String
LResult1 = Replace(DLookup("strUserDept", "tblUserRoles", "[strUserID]='" & Me.txtLoginName.Value & "'"), ",", " or")
Me.txtUserDept = LResult1




I have created a query that is supposed to pull the department names from the field above but i suspect that the entire entry is enclosed in quotes as it is a text field
So instead of "Medicine" or "Neurology" i believe it is returning "Medicine or Neurology" as one piece of criteria. Cant tell for sure as query returns no records.
I expect the department values to range from one to several.

Is there a way to substitute the word "or" with the Or operator?
Any suggestions?
Thanks.

[June7]

You are using a multi-value field. Nature of MVF is the field actually only stores pointers to records in a hidden table. Access formats the field to display values as if they are comma-separated but in reality there is no comma-separated string and no comma to replace. https://support.microsoft.com/en-us/...3-b6624e1e323a

I NEVER use MVF.

Even if you could substitute comma with OR, criteria would have to repeat the field like: Department = "Medicine" OR Department = "Neurology".

[ssanfu]

Here is an article by Colin Riddington (isladogs) as to why Multivalued Fields - BAD

[mike60smart]

I agree with Steve & June I never use Multi Value Fields
It is always best to store the required Departments as Records in a separate table which makes it very easy to query for the required values.

[orange]

Perhaps you could step back and tell us about your application and current table designs in simple English.
With some emphasis on your need for, and use of, multiple values. There may be design options to simplify (ease of maintenance, potential to move to different DBMS...), but readers need more info about the application and any plans that may exist.
As others have advised, most avoid multi-valued fields, and use traditional lookup tables when required.
Good luck with your project.

[CJ_London]

want to be able to use the multiple values in a query as "Or" Operators.

aside from the issues of using multi value fields you might want to consider using the IN operator. So rather than

myVal="A" OR myVal="B" OR myVal="C"....

you would use

myVal IN ("A","B","C".....)

i believe it is returning "Medicine or Neurology"

I doubt it if you have PK's in the table where these are listed. More likely to be returning

1 or 3 or 2

add the code
debug.print LResult1

after you have run your dlookup code

[jdashm]

Perhaps you could step back and tell us about your application and current table designs in simple English.
With some emphasis on your need for, and use of, multiple values. There may be design options to simplify (ease of maintenance, potential to move to different DBMS...), but readers need more info about the application and any plans that may exist.
As others have advised, most avoid multi-valued fields, and use traditional lookup tables when required.
Good luck with your project.

Thanks for your reply.
I have found a solution that seems to be working.
Posting this for anyone else who might come across this issue.
I found some code that replaced text with an In operator.
Here is an example...

Me.RecordSource = "SELECT * FROM qryFieldsForAgreementsForm_SOM " _
& " WHERE Department IN (" & Chr(34) & Replace([Forms]![_frmLoginVerify]![txtUserDept], " or ", Chr(34) & "," & Chr(34)) & Chr(34) & ") " _
& " AND Placeholder = False"

This runs when form is opened and replaced criteria that I originally placed in the query. Instead uses the code.

Thanks again for the consideration and reply.