473,836 Members | 1,440 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Check Boxes for Query Parameters

114 New Member
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 "ProcessingCent er" 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 6784
Scott Price
1,384 Recognized Expert Top Contributor
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 "ProcessingCent er" 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 New Member
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 Recognized Expert Top Contributor
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!Che ckBox1 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
2 New Member
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 New Member
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!Che ckBox1 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 Recognized Expert Top Contributor
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 Recognized Expert Top Contributor
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 New Member
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.Pr ocessCenter) LIKE IIf...etc look?

Thanks!
Sep 19 '07 #9
Scott Price
1,384 Recognized Expert Top Contributor
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

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

Similar topics

2
5269
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 that the creater of the even can make their own event in the database. When they do so a 2 tables are created. One for the entries such as names and the other is for the parameters of each event. The creator then goes in and makes each event...
1
3106
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 possibilities is something like:
10
15988
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 possibilities is something like:
5
7430
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 separate reports. Just to answer a few questions, yes, the subform is properly tied to the main form and they are all bound controls...been there, done that. Now, the purpose of a check box, as I see it is to have two logical conditions (checked =...
2
1765
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 dissappear in the query in data sheet view, and is replaced with 1's and 0's when i want the check boxes to have check marks. Thanks to anyone who helps.
5
1990
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 have a stored procedure set up to enter in the Yes or No answer.
7
1548
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 far. Dim displayedQues As Char = "Y" Dim nonDisplayedQues As Char = "N"
1
11517
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 the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so...
5
6750
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 (SQL -Change Card List). What I want to do is have the form open the report where a filter is set to use the values from the check box AND the value selected from the list box to generate the report. What I can't figure out is how to use the...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10845
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10549
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9376
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5650
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4019
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.