By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,187 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Check Boxes for Query Parameters

100+
P: 114
Good morning,

I have added several posts out here on a project on which I am working - thank you very much to everyone who has responded with ideas and solutions!

My new question:
I am drawing up some forms to use in place of "Enter Parameter Value" boxes to initiate queries. In essence the forms display all of the paramter choices (in combos, text boxes, and popup calendars). One search criterion I have is "ProcessingCenter" with five choices (5 different regions). I want to be able to search by all, some, or only one of these. In other words, the user can use a checkbox control to select Region1, or Region1 + Region2, or all 5 Regions, etc.

So, (A) how can I set the check boxes to have multiple checkboxes selected and (B) below is the code in the query I want to run - it runs fine by itself but with the form it only results in all 5 regions. What settings do I need to make in the checkbox control to accurately provide parameters to the query?

Thanks!

Expand|Select|Wrap|Line Numbers
  1. SELECT STARTS.ClientID, STARTS.Name, STARTS.SubmittedDate, STARTS.SalesCenter, Processing.ProcessingCenter, TASKS.Task, Status.Status_Full
  2. FROM STARTS, TASKS, Status, Processing
  3. WHERE (STARTS.ClientID=TASKS.ClientID)
  4. AND (TASKS.Status=Status.Status)
  5. AND (Processing.ProcessingCenter=STARTS.ProcessingCenter)
  6. AND ((Status.Status_Full) Like (Forms!TEST!Status_Full) & "*")
  7. AND ((Processing.ProcessingCenter) Like (Forms!TEST!Process) & "*")
  8. ORDER BY TASKS.Task;
  9.  
Sep 14 '07 #1
Share this Question
Share on Google+
15 Replies


Scott Price
Expert 100+
P: 1,384
Good morning,

I have added several posts out here on a project on which I am working - thank you very much to everyone who has responded with ideas and solutions!

My new question:
I am drawing up some forms to use in place of "Enter Parameter Value" boxes to initiate queries. In essence the forms display all of the paramter choices (in combos, text boxes, and popup calendars). One search criterion I have is "ProcessingCenter" with five choices (5 different regions). I want to be able to search by all, some, or only one of these. In other words, the user can use a checkbox control to select Region1, or Region1 + Region2, or all 5 Regions, etc.

So, (A) how can I set the check boxes to have multiple checkboxes selected and (B) below is the code in the query I want to run - it runs fine by itself but with the form it only results in all 5 regions. What settings do I need to make in the checkbox control to accurately provide parameters to the query?

Thanks!

Expand|Select|Wrap|Line Numbers
  1. SELECT STARTS.ClientID, STARTS.Name, STARTS.SubmittedDate, STARTS.SalesCenter, Processing.ProcessingCenter, TASKS.Task, Status.Status_Full
  2. FROM STARTS, TASKS, Status, Processing
  3. WHERE (STARTS.ClientID=TASKS.ClientID)
  4. AND (TASKS.Status=Status.Status)
  5. AND (Processing.ProcessingCenter=STARTS.ProcessingCenter)
  6. AND ((Status.Status_Full) Like (Forms!TEST!Status_Full) & "*")
  7. AND ((Processing.ProcessingCenter) Like (Forms!TEST!Process) & "*")
  8. ORDER BY TASKS.Task;
  9.  
Is your checkbox control an option group frame? If so, it will only allow one choice to be selected. So you could increase the choices possible to include all possible permutations, but that would eat up quite a lot of form real estate, so probably isn't the option you are looking for.

Placing your 6 check boxes on the screen without an option group is probably the way to go. Then in the criteria grid for your RegionID, you can put multiple
Expand|Select|Wrap|Line Numbers
  1. Like IIF(Forms![FormName]![CheckBox1], "*", 1)
  2. Like IIF(Forms![FormName]![CheckBox2], "*", 2)
statements for filtering based on the desired values. (The numbers I plugged into these iif statements are only for show... change them to reflect the RegionID numbers you wish to look up...)

Regards,
Scott
Sep 15 '07 #2

100+
P: 114
Is your checkbox control an option group frame? If so, it will only allow one choice to be selected. So you could increase the choices possible to include all possible permutations, but that would eat up quite a lot of form real estate, so probably isn't the option you are looking for.

Placing your 6 check boxes on the screen without an option group is probably the way to go. Then in the criteria grid for your RegionID, you can put multiple
Expand|Select|Wrap|Line Numbers
  1. Like IIF(Forms![FormName]![CheckBox1], "*", 1)
  2. Like IIF(Forms![FormName]![CheckBox2], "*", 2)
statements for filtering based on the desired values. (The numbers I plugged into these iif statements are only for show... change them to reflect the RegionID numbers you wish to look up...)

Regards,
Scott

Thanks, Scott.

I'm having a little trouble here. I agree that the option group frame is out if it only allows one checkbox selection. For the code...
I've tried two different things with different, but still not the wanted, results.
If I insert what you have provided into my code to look like this:
Expand|Select|Wrap|Line Numbers
  1. WHERE (STARTS.ClientID=TASKS.CLIENTID) And (TASKS.Status=Status.Status) And (TASKS.AssignedToRole=AssignedNames.Assigned) And (Processing.ProcessCenter=STARTS.ProcessCenter) And (([AssignedNames.LastName] & ", " & [AssignedNames.FirstName]) Like (Forms!TEST!FullName) & "*") And ((Status.Status_Full) Like (Forms!TEST!Status_Full) & "*") And ((STARTS.ScheduledRunDate) Between (Forms!TEST!Rundate1) And (Forms!TEST!Rundate2))
  2. And (((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox1,"*",1)) OR ((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox2,"*",2)) OR ((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox3,"*",3)))
  3. ORDER BY TASKS.AssignedDate, TASKS.Status, TASKS.CompletedDate; 
(1=Region1, 2=Region2, 3=Region3) then the query still returns all results.

If I insert what you have provided into my code to look like this:
Expand|Select|Wrap|Line Numbers
  1. WHERE (STARTS.ClientID=TASKS.CLIENTID) And (TASKS.Status=Status.Status) And (TASKS.AssignedToRole=AssignedNames.Assigned) And (Processing.ProcessCenter=STARTS.ProcessCenter) And (([AssignedNames.LastName] & ", " & [AssignedNames.FirstName]) Like (Forms!TEST!FullName) & "*") And ((Status.Status_Full) Like (Forms!TEST!Status_Full) & "*") And ((STARTS.ScheduledRunDate) Between (Forms!TEST!Rundate1) And (Forms!TEST!Rundate2))
  2. And ((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox1,"*",1)) AND ((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox2,"*",2)) AND ((Processing.ProcessCenter) Like IIf(Forms!TEST!CheckBox3,"*",3))
  3. ORDER BY TASKS.AssignedDate, TASKS.Status, TASKS.CompletedDate; 
then the query returns no results.

Which direction should I head from here? Thanks!
Sep 17 '07 #3

Scott Price
Expert 100+
P: 1,384
OK, I'm sorry it took me so long...

Here's a better go at it:

Expand|Select|Wrap|Line Numbers
  1. Like IIf([Forms]![form2]![CheckBox1]=-1,1,"*")
What you will have to do is use multiple nested IIF statements like the above to test for the various conditions.

To spell this version out: If and Only If Forms!form2!CheckBox1 is checked, then return value of 1, otherwise return all values.

If you have trouble with nesting the IIF's to get the results you want, let me know.

Regards,
Scott
Sep 17 '07 #4

Amicron
P: 2
Sorry I don't have the time right now to go into details, but you could also use a RECORDSET and a little VB code to do this, instead of a complicated query.

If you can't figure it out, let me know.


Richard
[Link Removed]
Sep 17 '07 #5

100+
P: 114
OK, I'm sorry it took me so long...

Here's a better go at it:

Expand|Select|Wrap|Line Numbers
  1. Like IIf([Forms]![form2]![CheckBox1]=-1,1,"*")
What you will have to do is use multiple nested IIF statements like the above to test for the various conditions.

To spell this version out: If and Only If Forms!form2!CheckBox1 is checked, then return value of 1, otherwise return all values.

If you have trouble with nesting the IIF's to get the results you want, let me know.

Regards,
Scott
Scott,
Thanks for your help so far. The WHERE and down part of my query looks as follows:
Expand|Select|Wrap|Line Numbers
  1. WHERE (STARTS.ClientID=TASKS.CLIENTID)
  2. And (TASKS.Status=Status.Status) And (TASKS.AssignedToRole=AssignedNames.Assigned)
  3. And (Processing.ProcessCenter=STARTS.ProcessCenter)
  4. And (([AssignedNames.LastName] & ", " & [AssignedNames.FirstName]) Like (Forms!TEST!FullName) & "*")
  5. And ((Status.Status_Full) Like (Forms!TEST!Status_Full) & "*")
  6. And ((STARTS.ScheduledRunDate) Between (Forms!TEST!Rundate1) And (Forms!TEST!Rundate2))
  7. And ((Processing.ProcessCenter) Like IIf([Forms]![TEST]![CheckBox1]=-1,1,"*")) And ((Processing.ProcessCenter) Like IIf([Forms]![TEST]![CheckBox2]=-1,2,"*")) And ((Processing.ProcessCenter) Like IIf([Forms]![TEST]![CheckBox3]=-1,3,"*"))
  8. ORDER BY TASKS.AssignedDate, TASKS.Status, TASKS.CompletedDate;
It still returns no results when any one, two, or all three checkboxes are selected. I guess I don't understand the nesting queries idea. Advice? Thanks!
Sep 18 '07 #6

Scott Price
Expert 100+
P: 1,384
I've got to step out this morning for a meeting, but will be back around this afternoon.

Let me think on this a bit... It might be time to explore a different direction. I'll see what I can come up with.

Regards,
Scott
Sep 18 '07 #7

Scott Price
Expert 100+
P: 1,384
I've got to step out this morning for a meeting, but will be back around this afternoon.

Let me think on this a bit... It might be time to explore a different direction. I'll see what I can come up with.

Regards,
Scott

Have a close look at this: I'm using this in my test db to see if I can reproduce your results. This works, giving the ability to choose any of three different combo boxes or any combination of them to choose which customerid to display:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCustomer.CustomerID, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.Damaged FROM tblCustomer
  2. WHERE (((tblCustomer.CustomerID) Like IIf([Forms]![form2]![CheckBox1]=-1,1,"") Or (tblCustomer.CustomerID) Like IIf([Forms]![form2]![checkbox2]=-1,2,"") Or (tblCustomer.CustomerID) Like IIf([Forms]![form2]![checkbox3]=-1,3,"")));
I had to delete the *, and change And to Or to make this work.

Regards,
Scott

p.s. please in the future when posting long sql statements, after wrapping them in the code tags like you are already doing, manually edit the first tag to look like this: [code=sql] This makes the code section easier to read with all the different colors, etc :-) Thanks!
Sep 18 '07 #8

100+
P: 114
Have a close look at this: I'm using this in my test db to see if I can reproduce your results. This works, giving the ability to choose any of three different combo boxes or any combination of them to choose which customerid to display:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCustomer.CustomerID, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.Damaged FROM tblCustomer
  2. WHERE (((tblCustomer.CustomerID) Like IIf([Forms]![form2]![CheckBox1]=-1,1,"") Or (tblCustomer.CustomerID) Like IIf([Forms]![form2]![checkbox2]=-1,2,"") Or (tblCustomer.CustomerID) Like IIf([Forms]![form2]![checkbox3]=-1,3,"")));
I had to delete the *, and change And to Or to make this work.

Regards,
Scott

p.s. please in the future when posting long sql statements, after wrapping them in the code tags like you are already doing, manually edit the first tag to look like this: [code=sql] This makes the code section easier to read with all the different colors, etc :-) Thanks!
Thank you, Scott, for your help. I have it up and running now - works great. Thanks for the tip about formatting SQL statements on this forum - I can see how coloring the keywords would be helpful to people answering my questions!

On a related subject...I have another database in which I want to accomplish the same thing: choosing multiple options of the same criterion for a query. Only this time the list is more than a handful of options - it's hundreds of options. So checkboxes become impractical and I think using a series of combo boxes would be a better choice (because the user will select only up to 10 choices from the hundreds available at a time). Do you concur with this idea?

I need some helping around the combos then and, to keep things simple, let's assume I have the same fields, tables, forms, etc as above and am looking at 3 combo boxes.

My question: In my WHERE clause, how will the ((Processing.ProcessCenter) LIKE IIf...etc look?

Thanks!
Sep 19 '07 #9

Scott Price
Expert 100+
P: 1,384
Well, you could set it up with multiple cascading combo boxes, where the selection in #2 is dependent on the choice in #1 and so on, but that's going to get complicated in the table design part :-)

However, with hundreds of choices, you probably don't have many other options... How are these sorted into categories?

The Where statement to populate your combo box row source may depend on other factors, so you might not need to use the iif's (although you can use any valid sql statement as a row source for your combo box.)

Regards,
Scott
Sep 19 '07 #10

100+
P: 114
Well, you could set it up with multiple cascading combo boxes, where the selection in #2 is dependent on the choice in #1 and so on, but that's going to get complicated in the table design part :-)

However, with hundreds of choices, you probably don't have many other options... How are these sorted into categories?

The Where statement to populate your combo box row source may depend on other factors, so you might not need to use the iif's (although you can use any valid sql statement as a row source for your combo box.)

Regards,
Scott
No categories - the data are the names of cities within a few states. The table has a field for the city and the state (as well as an ID number) so I suppose the data could be grouped by state.

What happens: The user might...
(1) select CityA from combo1 and CityF from combo2 and CityQ from combo3,
-or-
(2) select CityA from combo1,
-or-
(3) select CityB from combo1 and CityJ from combo2, etc.

Each combo has the same list of cities and the user simply goes through the combos in order using as many of them as he or she needs. I'm fine with the idea that a city selected in combo1 still appears in combo2 because I know that will be much easier.
Sep 19 '07 #11

Scott Price
Expert 100+
P: 1,384
No categories - the data are the names of cities within a few states. The table has a field for the city and the state (as well as an ID number) so I suppose the data could be grouped by state.

What happens: The user might...
(1) select CityA from combo1 and CityF from combo2 and CityQ from combo3,
-or-
(2) select CityA from combo1,
-or-
(3) select CityB from combo1 and CityJ from combo2, etc.

Each combo has the same list of cities and the user simply goes through the combos in order using as many of them as he or she needs. I'm fine with the idea that a city selected in combo1 still appears in combo2 because I know that will be much easier.
Are these city choices intended to be used as search criteria also?

Another idea is to use one multiselect list box grouped by state. Then we can write some fairly simple code to capture the multiple choices and concatenate them together to use as a search criteria in a query.

I think this would be a bit simpler and possibly a bit more elegant than multiple combo boxes.

Regards,
Scott
Sep 19 '07 #12

100+
P: 114
Are these city choices intended to be used as search criteria also?

Another idea is to use one multiselect list box grouped by state. Then we can write some fairly simple code to capture the multiple choices and concatenate them together to use as a search criteria in a query.

I think this would be a bit simpler and possibly a bit more elegant than multiple combo boxes.

Regards,
Scott
Hello, Scott, I was out of the office most of yesterday - thanks for your quick reply.

Yes, in this database these cities are the only search criteria - the results of a search are city, state, zip, website, contact info.
I like the idea of listboxes grouped by state but I don't think that will work quite as intended.
The purpose of the database is to search by city name in this form to create a report listing the selected cities with contact info, etc (as listed above). Users might select anywhere between 1-10 cities but never more. I'm thinking it might not be best to group by state. With a combo box the user can start typing the name and it will go to matching items in the combo (perhaps that can happen with a listbox as well - I'm not sure). Does that make sense to you? Let me know what you think.

Brian
Sep 20 '07 #13

Scott Price
Expert 100+
P: 1,384
Hello, Scott, I was out of the office most of yesterday - thanks for your quick reply.

Yes, in this database these cities are the only search criteria - the results of a search are city, state, zip, website, contact info.
I like the idea of listboxes grouped by state but I don't think that will work quite as intended.
The purpose of the database is to search by city name in this form to create a report listing the selected cities with contact info, etc (as listed above). Users might select anywhere between 1-10 cities but never more. I'm thinking it might not be best to group by state. With a combo box the user can start typing the name and it will go to matching items in the combo (perhaps that can happen with a listbox as well - I'm not sure). Does that make sense to you? Let me know what you think.

Brian
I was thinking of only one list box, with the group by option set in the row source to group by state. However, with many many cities, this could result in lots of scrolling to get the city/state combinations that you want.

This link shows a sample database by Allen Browne that contains a find as you type function, could be something like what you're looking for?

http://allenbrowne.com/AppFindAsUType.html

Regards,
Scott
Sep 20 '07 #14

100+
P: 114
I was thinking of only one list box, with the group by option set in the row source to group by state. However, with many many cities, this could result in lots of scrolling to get the city/state combinations that you want.

This link shows a sample database by Allen Browne that contains a find as you type function, could be something like what you're looking for?

http://allenbrowne.com/AppFindAsUType.html

Regards,
Scott

Back to this question...
Thanks for all of your help, Scott, but I was making it more complicated than I needed - needless to say I figured it out. Here is an example of what I was trying to do (this just uses process centers from my original question as an example):

On a form with two textboxes (Rundate1 and Rundate2) and nine combo boxes (ProcessCombo1-9) all unbound.

The record source for the combo boxes is a table listing processing centers.

The form's code is:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub OK_Click()
  3.   Me.Visible = False
  4.   DoCmd.OpenQuery "TEST", acViewNormal, acEdit
  5.   DoCmd.close acForm, "TEST"
  6. End Sub
  7. Private Sub Cancel_Click()
  8.   DoCmd.close 'Close Form
  9. End Sub
The query SQL is:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT STARTS.ClientID, STARTS.RunDate, STARTS.ProcessCenter
  2. FROM STARTS
  3. WHERE ((STARTS.ScheduledRunDate) Between (Forms!TEST!Rundate1) And (Forms!TEST!Rundate2))
  4. AND ((STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo1) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo2) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo3) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo4) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo5) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo6) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo7) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo8) Or (STARTS.ProcessCenter) Like (Forms!TEST!ProcessCombo9))
  5. ORDER BY STARTS.RunDate;
So when a user opens the form, enters the run date range, selects as few or as many processing centers as he or she wants, clicks OK - results in all clients with any and all processing centers that the user selected from the combo boxes.

Thank you again, so much, for all of your help!
Oct 3 '07 #15

Scott Price
Expert 100+
P: 1,384
Glad you got it to work, and thanks for posting back with your solution!

Regards,
Scott
Oct 3 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.