Add multiple items to search form?

[templeowls]

I've got a form that allows the user to input information to filter down a report. The DB is employee complaints so the field in the form lets the user put in an employee's name to filter down to complaint just from that one person.

Below is my code. I was hoping there's a way to allow users to put 2 or more names in the field, and maybe seperate them by commas or semicolons. And then the report shows records for both the names.

Any suggestions on how to code that?

If Not IsNull(frm!txtEmployeeName) Then        
If strWhere <> "" Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[EmployeeName] LIKE '*" & frm!txtEmployeeName & "*'"
End If

[CJ_London]

you need to use OR or IN

but not clear if user is entering a partial name or a full name - provide some example data of what a user might input and what you expect to get returned

[orange]

More info please.
Can you give us a sample showing initial items and some additional item(s) with expected result?

[Gicu]

I would recommend a different approach that would make it a lot easier to code and more flexible: create a local table to list the employees names (or better their numeric unique ids) and a yes\no field named IncludeInReport or similar. Then create a simple datasheet form based on this table and use that to replace the textbox you currently use for user input (as a subform). Now the user can select multiple employees in the subform (use combo's to display the names but bind the ID field); finally add the table to the report's record source and join on the employee ID and use True in the criteria row for the IncludeInReport field.

You could also use a multi-select listbox but those are harder to code as you would have to loop through the selected items and dynamically build the SQL statement for the report.

Cheers,

[templeowls]

you need to use OR or IN

but not clear if user is entering a partial name or a full name - provide some example data of what a user might input and what you expect to get returned

Ideally, partial names. I was hoping that I could input 'John; Matthew' and then all records with John or Matthew in the 'EmployeeName' field would populate

[CJ_London]

OK - brief answer so a brief response

You've answered your own question - you need to use 'OR'

name=like '*john*' OR name like '*matthew*'

all you have to do is split the user input and create the string. To split, use the split function