Multiple selections in query criteria 2

[KarenMO]

Hello again -

I'm trying to create a query for a report that will allow the user to select multiple items in the selection criteria (i.e., job numbers 100, 101, 105). I can't figure out what I'm doing wrong, but I'm having no success. Yes, this is related to my previous post about the job number form (there will be multiple buttons for various report choices on the form). Thanks in advance - again - for any help! - Karen

 

[GingerR]

What exactly is "wrong"? Is this a List Box? What is your query's code? What is not working?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[KarenMO]

Hi, Ginger - There are four fields in the database (and thus in the query). One of them is JobNo and that is the selection criteria field. In the query, I simply have "[Enter one or more job numbers]" - which then brings up the window to enter the selection criteria for the query or the report based on the query. If I enter a single job number, it comes up fine. But if I want job numbers 100, 101, and 105, for example, I get absolutely nothing. Thanks for your response! - Karen

 

[GingerR]

So in the table, there are job numbers such as

101
102
103

and you are entering

101, 102, 103

and expecting all three to show up? There is nothing that matches "101, 102, 103", right?

A better thing to do is to make a list box on a form, pick the items you want to see and hit a button that runs the query/report.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

Go in the SQL view pane and modify the WHERE clause by replacing something like this:
JobNo = [Enter one or more job numbers]
with this:
InStr(',' & [Enter one or more job numbers] & ',', ',' & JobNo & ',') > 0

And then try with 100,101,105 (without space) in the popup

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KarenMO]

>>There is nothing that matches "101, 102, 103", right?

I was actually trying it with "or" - but you're correct that nothing shows up.

I had considered using a list box, but since the form will eventually have many thousands of records, with only a few entries for each unique job number, I thought it would be cumbersome for the users to do it via a list box. The job numbers will be useful for a very short window of time - a week or two - then the summary will be printed and that particular job number not likely need to be accessed again, and dozens of new job numbers will be generated every day. A list box would thus generate many, many records to scroll through that are no longer active. (But the client doesn't want to build any functionality into the database that renders a record "inactive" so as not to clutter the view.)

Thanks again. - Karen

 

[GingerR]

Try PHV's solution

Also, if you'd be putting in a range of numbers, you can also do:

Between [Enter First Job:] and [Enter Last Job:]

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[KarenMO]

Ginger and PHV - I think these last two suggestions are going to do the trick. This is just what I needed. Thanks so much! - Karen