473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Check Boxes for Query Parameters

114 100+
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
15 6732
Scott Price
1,384 Expert 1GB
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
martin DH
114 100+
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
1,384 Expert 1GB
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
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
martin DH
114 100+
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
1,384 Expert 1GB
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
1,384 Expert 1GB
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
martin DH
114 100+
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
1,384 Expert 1GB
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
martin DH
114 100+
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
1,384 Expert 1GB
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
martin DH
114 100+
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
1,384 Expert 1GB
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
martin DH
114 100+
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
1,384 Expert 1GB
Glad you got it to work, and thanks for posting back with your solution!

Regards,
Scott
Oct 3 '07 #16

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

Similar topics

2
by: Ben | last post by:
My current project requires me to create part of a form that is created on the fly. The project consists a list of entries to an event. The name and address and such is easy. The design is detup so...
1
by: Jim in Arizona | last post by:
I'm having dificulty figuring out how to process multiple check boxes on a web form. Let's say I have three check boxes: cbox1 cbox2 cbox3 The only way I can think of to code the...
10
by: Jim in Arizona | last post by:
I'm having dificulty figuring out how to process multiple check boxes on a web form. Let's say I have three check boxes: cbox1 cbox2 cbox3 The only way I can think of to code the...
5
by: jimc52 | last post by:
Hello Everyone: I am hoping one of the gurus here will give me some help. I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on...
2
by: vibee | last post by:
this might be a simple question but how do i assign values to a check box in a query condition, i have the following so far: Required: IIf(="existing",True,False) The problem is the check boxes...
5
by: JJ297 | last post by:
Hello can someone assist me... I've added two check boxes to my page <asp:CheckBox ID="ChkYes" runat="server" Text="Yes" /><br /> <asp:CheckBox ID="ChkNo" runat="server" Text="No" / I...
7
by: JJ297 | last post by:
Can't get null into the database. I'm trying to achieve... if displayedQues = "Y" then I want nondisplayedques to go into the database as null. What do I need to do? This is what I have thus...
1
by: Euge | last post by:
Hi, I really hope someone will be able to help me with this one as I am sure im just missing something simple. I have an unbound form which has 20 yes/no unbound check boxes. The purpose of...
5
by: Andrew Meador | last post by:
I have a form (Change Card List by Status) with a check box (cboNOT) and a list box (lstStatus). There is an Open Report button that opens a report (Report - Change Card List) which uses a query...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.