enter multiple items in parameter query Access 2016

[Jacqueline]

I need to run a query where the user can enter multiple items separated by a comma. The code was working in my older versions, but I cannot get it to work in a new database.



The query prompts the user for multiple class item numbers that will run class rosters for one or as many items as they enter. If there is an easier way to do this, I'm open to suggestions.

The code I used in SQL view is listed below.

(("," & [Enter Item number, or numbers seperated with a comma no spaces] & ",") Like "*[,]" & [ITEM] & "[, ]*")



It worked and still works in my old database but not the new server version of Access 2016




Thanks, everyone
Jacqueline

 

[arnelgp]

Are you directly opening the query or using a form?

If direct display of query, why not use a datasheet form and on open event modify the rowsource.
On the sample form, initially it shows no record. Then ask for comma separated value.
See the code.

But see the table1 first so you know which IDs to enter.

 

[Jacqueline]

I will take a look at this. I'm not sure it will work becaue I am working with read only tables from the student management system.

Thanks,

Jacqueline

 

[arnelgp]

maybe it will since you have Select privillege on the db.

 

[MajP]

You could also add to arnelgp's approach and instead of typing in the form you could provide a multiselect listbox, and you would build the recordsource by looping the items selected. That way you can ensure you get (Class 1 instead of them typeing Class One, or Class1, etc.) and it can provide only a list of the available classes.

 

[isladogs]

Similar to MajP's reply, have you tried using the IN operator in your query as used with a multiselect listbox

EDIT - I've since looked at arnel's example and realised that's exactly what he's done. Sorry.