Using Like to prompt input of multiple values

[eskybel]

Hello,



I have a query that returns values and displays them in a report, in which the user will be prompted with a box to enter a value to use as criteria, such as if I want the report run for a specific sales rep, the criteria is like [Rep #?]. There is no field called Rep #?, so obviously it asks for input. What we typically do is either enter each number separately, or use * to pull everything.

I would like to know how, if possible, a user could enter multiple values in the single input box.

So for example, we have a rep that has two different rep numbers. I would like to populate the report with info based on his two rep numbers, so when the report prompts for [Rep #?], the user could enter something like "01 and 02."

I've tried:
01,02
01, 02
01 or 02

Is that possible?

[orange]

You could adjust your design to correct this -- or did you intend for rep number to NOT be unique?

we have a rep that has two different rep numbers.

It does sound like a design issue.

It would be helpful if you could post the actual SQL of the query involved.
Is rep number a number or text/string?
Perhaps 1 OR 2, but we need to see the SQL.

[eskybel]

You could adjust your design to correct this -- or did you intend for rep number to NOT be unique?

It does sound like a design issue.

It would be helpful if you could post the actual SQL of the query involved.
Is rep number a number or text/string?
Perhaps 1 OR 2, but we need to see the SQL.

Rep number is a number.

Because of some changes on the sales rep side, their name and some specific information to them changed. So, they were granted a new Rep # to go forward with so we could assign sales of parts to their new Rep #. But, there are also sales waiting to be finalized under their old number. So, right now, they have two numbers. We generate a sales report for each specific Rep, but now they have two numbers, so we either have to generate the report twice for them, or we find a way to combine them, based on user input. Eventually they won't have two numbers, but for the foreseeable, they do.

I just used the Query Design button in Access, no manual code. But, I guess here's the piece that prompts the user...if it helps?
WHERE (((xxxxx.GROUPID) Like [Rep #?])

[orange]

If you know that rep number 20 and 49 refer to the same person, - let me say if you know
which numbers refer to the specific rep - then I'm sure this can be done.
How many sales reps? Do you have a list of the Sales reps and their numbers? Do you have any 2 John Smiths etc?
Are the First/LastName combos all unique?
Need to create a table that has SalesRep and number, then some joining to get the proper data.

[eskybel]

Ok, thank you for the update.

We only have two reps, but 3 numbers, as an example. It's pretty simple I suppose. If there wasn't an easy, built in way to just type "01 or 06" in the first prompt, then it's likely not worth fixing the design to make it work. We can just have two reports. Thank you!

[orange]

I wouldn't give up that easy.

It's worth the exercise to see if it can be done, whether you choose to use it or not.

Post a few records

[orange]

I have a sample with Authors and Books (you can use Authors as RepNumbers)

In the author list below I have given Jones 2 ids - 5 and 12

Query to get all books for the author regardless of his/her AuthorId (repNumber)

SELECT authors.author_name
, authors.author_id
, BOOK.BookTitle
FROM authors INNER JOIN 
(BOOK INNER JOIN BOOKAuthors ON
 BOOK.BookId = BOOKAuthors.BookID) ON
 authors.author_id = BOOKAuthors.AuthorId
WHERE (((authors.author_name) 
In (select distinct author_name from authors)))
ORDER BY authors.author_name;

author_id	author_name
4	samuels
5	Jones
6	Alyme
7	Willington
8	Arnold
9	Polivka
12	Jones

Results of the query in the attached jpg

Good luck with your project