Using MS Access/Multiple criteria with

Advertisement


Question
QUESTION: I have a report that uses "Like" in the criteria for the query the report is based on. It will bring up multiple records for a specific job that the user inputs. I can bring up records for a specific job by inputting that job number or records for all jobs by inputting   *  . Sometimes I want to bring up records for two, three or more jobs but not all jobs. I have tried inputting multiple jobs in the parameter window that comes up with the "Like" criteria in the query but get no results. I've tried  ABC, DEF  also   ABC OR DEF   and several other combinations. Is there a way to bring up more than one job's records without hard wiring to bring up three job's records and just not input the third job if I want records two jobs at a particular time?

Thank you,
Ed

ANSWER: You have to use a different criteria. You can use an IN clause with a list of values. For example:
fieldname IN(123,456,789)

This article may help you:

http://access.mvps.org/access/forms/frm0007.htm

---------- FOLLOW-UP ----------

QUESTION: I've done a poor job of explaining, my apologies. When I open a report it uses a query to return the records I want. In that query one of the field names is "JobNo". In the criteria under JobNo I have the statement  "Like [Job Number, Please]". When I open the report I first get a parameter window asking which job number I want. I can put the particular job number in and get the records for that specific job or I can input the asterisk and get the records for all job numbers. Of the jobs in the database I may want two or three or four specific jobs.

I gather from your response that I may get what I want by using "IN" in the criteria in place of the "Like" but the various ways I have tried it I get no results.

I went to the link you provided and I didn't get anything from that I could use to help me with this.

I'm most likely trying to use the "IN" incorrectly, can you provide guidance/examples.

Thank you,
Ed

Answer
No you explained it fine.

First I almost NEVER use Parameter prompt queries. They are very limiting. You are better off using a form to enter the criteria used by your query. Many people do use the LIKE operator as you are to either pull one or all values. But the Like operator should only be used when you want to apply a wild card.

The article I linked to is almost exactly what you want. You create a multi-select list box and then select the JOBs you want. If you followed the instructions as closely as possible you should have gotten it to work.

If you are having problems. I would suggest starting a thread at askmehelpdesk.com and let me know what exactly isn't working.

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.