Multiple Criteria in Query to prompt for input 1

[BYarnn]

We commonly use queries (and the same ones in reports) that prompt for user input and return the results based on the answer. The problem is when there may be more than one answer desired. An example- The query prompts for "What is your favorite fruit?" The answer might be apple, but it may be apples and oranges or apples, oranges, and pears.

How can i prompt for that so that the multipkle (types of fruit liked) are returned?

 

[BYarnn]

More specifically-more than one prompt can be used in a single column of the query, but what if you want to narrow down the reults by ALSO using another column?

For example If you use the ? "what is your favorite fruit?" The answer might be apple, but it may be apples and oranges or apples, oranges, and pears. But what if you also wanted to know if the fruits were grown in Florida or say California. So u prompt for favorite fruit in one column but another column is the State -- how can you prompt in that column also and get say apples and oranges in California only (not Fla, etc)

 

[PaulBricker]

Well, you can use multiple prompts but that seems somewhat clunky.
[Enter Name] or [Enter a second name] or [Enter a second name] Is Null or [Enter a third name] or [Enter a third name] Is Null.
Then combining those with other fields, really strings things out.

My suggestion would be to use a form to input all the parameters you want. Then just click a button to run the query.

HTH

Paul

 

[IGPCS]

what i would do as freindly user i would create a form with a list of the answers and in the criteria of your query use where the form is marked with a check or something like this

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[BYarnn]

OK. I agree it's clunky (good choice of words!) A form would seem to be good idea. I haven't done it that way before so 2 ?'s :

1--- does the form have all the fields from the query (so u can use any one or more field? OR does it just have the fields u want to prompt for? (ie, if u put in say too many fields and the user doesn't want to use all of the fields (like he wants to know the fruit but doesn't care where it came from) will using all of one field still produce results based on one, two, three, etc fields??

2-- how do I create the query-i.e., how is the query created to represent all or one of the fields being completed (if you use say a button to run a single query its pretty straight forward, what happens when you have multiple criteria on the form? DOn't know if that makes sence.

 

[BYarnn]

IGPCS- I don't quite understand this part: "use where the form is marked with a check or something like this"

 

[IGPCS]

what i will do i will create a form based on a table or query with the question and the answers then base your query with where the table/query (that the form is based on) is marked or selected

hope you get it, this is a very simple procedure

happy holidays

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[IGPCS]

well you can add a field in you table thats a yes/no field and check it on the form so the query will take where the fields are checked

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[BYarnn]

Happy Holidays to u also!!

OK that was a little clearer- sorry to belabor this- I'm visual so I just have to do it once and I will then be ok.

So one more ?- if I use the form to fill in the table, I'm trying to picture what the query will look like- in other words in a simple query if I used "apple" for my criteria in the field labeled "fruit" it will return the answer but if the user fills in the form and selects apple and californa (type of fruit and location)and how is the query structured to selct records based on both criteria (vs not selecting anything) Does that make sence? Thanks for your patience. I'm not really new to access, just this concept

 

[IGPCS]

in the query builder type the criteria in the second line of the criteria after the or line this should do i or you can use there and iif([form].[field] > "" ,[form].[field])



I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[BYarnn]

In the process of reading your responces, trying them, researching Microsoft and experimenting, I ran across the solution to my problem, although I have not yet tryed implementing it in a form.

Refer to "

http://support.microsoft.com/kb/210530/en-us"

and specifically the following expression "InStr([Last Names separated by commas,Blank=All],[LastName])"

I took that expression and incerted it into the FIELD of my query. I also duplicated the same field next to the column with the expression. I changed the "[LastName]" to correspond to what the field was really called and did the same thing for as many fields/columns in the query.
The result is a series of propmts for parameters, the # of which depends on the number of expressions used.

When prompted, type in the data corresponding to what you want and what is in that column and separate each by commas. For example, if you want a person named Smith and a person named Rogers, when you are prompted by "[Last Names separated by commas,Blank=All]" you type smith, rogers and all last names like those are returned. If you want smith and rogers who work in say sales, when you are prompted the the parameter you entered [Divisions separated by commas,Blank=All]" , you enter "sales" and get anyone named smith or rogers that works in sales.

It appears that you can do this in multiple columns (I tryed up to 4 different parameters to really narrow my request down. Now if I tie that query to a report I can answer the propmts and get a very custom report. I do want to test more, however.

 

[PaulBricker]

Well done BYarnn. Thanks for posting that solution. It looks like just what you need.

Paul