Need to select one or many or all in a query ! 2

maverick · Jan 17, 2000

I have a report to print, but... 
I need to be able to select all records with TX., CA. or as many as I need at the time. 
I'm not sure how to do this...Query? criteria? 
Parameter subform? I'm blank on this...for some reason. 
 
I know in the query criteria you can put " Like[Enter State] 
but you only get one choice! 
and it will ask you to enter state, but what if I want CA TX OR and tomorrow I will need just CA, and the next day I need all state. ???? 
 
Thanks...

elizabeth · Jan 17, 2000

The criteria needs to be in the query that your report is based on. If you just want to do it manually, use " in set()" on your criteria line of your query in your state column. (Go in design view of your report and click on the Properties icon, then the Data tab then the Data Source property.) For example: 
in set (TX) 
in set (TX, OR, CA) 
in set (CA, TX) 
 
There's a complete article on how to do this through code with a listbox at <A HREF="

http://www.zdjournals.com/ima/s_ima/9911/ima99b1.htm"

TARGET="_new">

http://www.zdjournals.com/ima/s_ima/9911/ima99b1.htm</A>

DougP · Jan 17, 2000

Also If you want the query to prompt you 
put this in the box with brackets around it. 
[Enter State] 
Where what is inside the brakets will show as the question asked. 
If you type "TX" then it will show Texas

maverick · Jan 17, 2000

first of all; 
Thanks for replying !!! 
 
I knew this would be hard to explain; 
I need to create a report that I can select one, many or all of the states in a query column to avoid having to make more than one column with criteria that says [Enter State]. 
 
I'm checking out the "in set()" idea now !

maverick · Jan 18, 2000

Elizabeth ; is there any way I can get that report from you , it appears that site charges for access...

elizabeth · Jan 19, 2000

Sorry! I get the magazine so apparently I've already paid for it. If they're charging for it I probobly shouldn't reporduce it in a public forum. If you can't get into this one, just let me know and I'll paraphrase for you.

maverick · Jan 19, 2000

I appreciate your honesty and Integrity !!! 
 
I've ordered the magazine... but they didn't guive me a password to enter the site, does that come with the magazine ? 
 
Thanks for your help, this one is kicking my butt ! 
 
I am a Troubleshooter HW, SW, Networks ten years.., now Access, But this is tuff ! 
 
It was time to get into programing anyway ! 
 
ta,

DougP · Jan 19, 2000

So you want to pick mutiple states say "give me items from Texas and Florida and California"? 
but be able to change the number of states and and which states as you mentioned above in a Combo box or something? 
 
Or in other words have it build a SQL statement like 
....Where State = "TX" and "CA" and "FL .... 
 
Is that what you want to do?

maverick · Jan 19, 2000

That sounds about right, 
have a form where I can enter into feilds, 
 
feild one; tx,ca,or 
feild two; Smith 
 
and get a report back that gives me all records with "tx,ca,or" in the state feild of that table that have the last name "smith".. 
 
and have an additional button on the same form that will give all records in the report.. 
 
You guys are great, I never thought I would get good responses back ! 
 
Thanks !

elizabeth · Jan 19, 2000

I would ask them to email the complete article to you, but the gist of it is: 
1. Create a report. 
2. Create a form with a list box and an Open Report button. 
3. Populate the list box with the choices using a query as the Row Source. 
4. Add code to the OnClick event of the button. The code dims variables (dim Forms!YourFormName!YourListBoxName as "ctl&quot

, loops through each of the selected items in the list box, then opens the report. The crux is the loop and following 2 lines: 
 
If ctl.ItemsSelected.Count = 0 then 
' insert a message box asking user to select state, then exit) 
Else 
For each var in ctl.ItemsSelected 
temp = Chr(39) & ctl>itemDate(var) & Chr(39) & "," 
strCriteria = strCriteria & temp 
nextvar 
end if 
strCriteria = Left$(strCriteria, Len(strCriteria)-1) 
strCriteria = "[strState] In (" & strCriteria & &quot

" 
 
strState refers to the name of the state field in your table. 
Hope I haven't made any serious typos!

elizabeth · Jan 19, 2000

Typo! ">" should be "." 
Also should have mentioned strCriteria is a parm in DoCmd.OpenReport

elizabeth · Jan 19, 2000

Ahhhh... another typo! "ctl>itemDate(var)" should be "ctl.ItemData(var)

maverick · Jan 19, 2000

Getting an error on dim line ?

elizabeth · Jan 20, 2000

Sorry, I shouldn't have made the assumption you had some familiarity with VBA. If not, you need to get some background first or get step-by-step instructuions, such as are in the (lengthy) IMA article. If you've bought a subscription, why don't you get in touch with them to see if they can help you with that.

maverick · Jan 20, 2000

Thank you all very much for your comments; 
 
I have always thought that most VBA programers in general over-complicate simple tasks..not to say you guys do ! 
I have figured out a simple way to acomplish this task; 
 
Create a selection form with multiple combo boxes, as many as are needed for your choices, that show you the data your querying, 
Example; "combobox1 is Unbound but named [state1]" and so on,,,[State2] etc... 
 
Create a query that pulls the info you need for the report, in one of the columns in your query type the following: 
 
IIf(IsNull([Forms]![Selection frm]![state1]),True,IIf([tblPeople].[State] In ([Forms]![Selection frm]![state1],[Forms]![Selection frm]![state2],[Forms]![Selection frm]![state3],[Forms]![Selection frm]![state4],[Forms]![Selection frm]![state5]),True,False)) 
 
it will label it Exp1 don't worry that will go away by it self! 
The SHOW check box should be clear! 
 
'the people table should be in your qurey' 
 
No code required ! just simple query criteria ... 
 
As I thought it could be done.... 
 
Don't forget your print button on the selection form 
and to have the report pull from your new query.. 
 
Thank you very much "everyone" who tried to help, this was very important to me !!!! 
 
Mav out,,

maverick · Jan 20, 2000

I missed one thing; 
in the criteria column with the code typed above 
the criteria is True and the stuff typed above goes in the first row of that column..no check in show-box 
 
Thanks Guys !!!

elizabeth · Jan 21, 2000

I'm glad you found a method that works for you. Thank you for posting your solution! 
 
BTW, maybe one reason other people's solutions seem overcomplicated is that they have different criteria for solving a problem. For instance, one person may be most interested in simplifying the User Interface to make the app more palatable for the customer. Another might be most concerned with very robust error checking, as they won't be around if the user does something unexpected. Most professional programmers have at least a token interest in writing code that runs efficiently, and can be easily maintained. In some cases the goal is to do something quickly, using existing skills, because the problem is urgent. I think the best programmers try to optimize, considering all these things. 
 
And one of the great things about these forums is that you get to see a number of different approaches. Even when one person has posted a workable solution, you often see a second or even third approach posted. I love to see that.

DougP · Jan 21, 2000

Very Well put Elizabeth.

maverick · Jan 21, 2000

Thanks again for your input !

mbuell · Jan 21, 2000

Forgive me if I don't understand the question, but it seems to me that the kiss answer to this is just to use "OR" in the query criteria. I've used numerous criteria in a single column many times. The SQL would go something like WHERE table.state LIKE "CA" OR LIKE "OR" OR LIKE "FL", etc. You easily design a popup form and concatenate a SQL statement or filter string. If you were doing this in query design, you can use the OR directly in the criteria in a single column. 
 
Mark

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need to select one or many or all in a query ! 2

MIS

IS-IT--Management

MIS

MIS

MIS

IS-IT--Management

MIS

MIS

MIS

IS-IT--Management

IS-IT--Management

IS-IT--Management

MIS

IS-IT--Management

MIS

MIS

IS-IT--Management

MIS

MIS

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor