If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
question driven query
Steph -
You could build a form that has the criteria checkboxes on them, and whatever other criteria you need. Then when someone clicks on the query, bring up this form, and then run the query based on the values in the form's checkboxes. -- Daryl S "Steph" wrote: Hi All, I need a little expert advice here. I have a query that pulls from just one table. What I would like is once the query is clicked, have a series of questions, prob 3 or so to drive the criteria. In the past I have done this for dates but cant figure this one out. the fields to be queried are all checkboxes. IE: Was the voice Male or Female?(they are both checkboxes) Was it a child? (a checkbox) Which Antennae was it heard on? (6 total options, all checkboxes. thanks so much in advance!! Steph |
#2
|
|||
|
|||
question driven query
Steph -
Create a new form - no record source for the form. Add the checkboxes with appropriate descriptions and control names. Then in your query, in the criteria row below each field, add the value from the form. You can do this using the 'build' button in query design and navigate to the form and proper checkbox. These will look something like this: Forms!NewFormName.[cbxFirstYesNo] Where you would substitute the name of your form for NewFormName, and the name of the first check box (not the field name, but the name of the checkbox) for cbxFirstYes. These will work like parameters if the form is not open in form mode, so you can test your query by entering True or False while the form is in design mode or closed. If that isn't enough to get you started, repost with a description of what you have and where you need the help. -- Daryl S "Steph" wrote: Hi Daryl, thanks for the response. I love that idea, which will definitely get me my desired goal (even better). I am pretty comfortable with forms coding and queries, but do not know how to do what you mentioned. can you get me started? Thanks again! "Daryl S" wrote: Steph - You could build a form that has the criteria checkboxes on them, and whatever other criteria you need. Then when someone clicks on the query, bring up this form, and then run the query based on the values in the form's checkboxes. -- Daryl S "Steph" wrote: Hi All, I need a little expert advice here. I have a query that pulls from just one table. What I would like is once the query is clicked, have a series of questions, prob 3 or so to drive the criteria. In the past I have done this for dates but cant figure this one out. the fields to be queried are all checkboxes. IE: Was the voice Male or Female?(they are both checkboxes) Was it a child? (a checkbox) Which Antennae was it heard on? (6 total options, all checkboxes. thanks so much in advance!! Steph |
#3
|
|||
|
|||
question driven query
Steph -
Did you want the [Forms]![frmHoaxQuery]![child] criteria for HoaxChild instead of HoaxMale? You will need each criteria under it's proper field. You are on the right track! -- Daryl S "Steph" wrote: Thanks so much daryl, you have me on the right track. a little snag. This is what I have: A form named frmHoaxQuery, (no record source) put in some check boxes with my criteria. my qry called qryProb_Hoax (the one I had before). In there. I have this under the MALE Voice field: [Forms]![frmHoaxQuery]![child] However it is returning nothing when i know there should be some. I think I am missing your instructions on the "yes" part! Here is the code of my query: Thanks so much! SELECT tblSENEIncidentLogCY.IncidentID, tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.incident_DTG, tblSENEIncidentLogCY.[Case Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS], tblSENEIncidentLogCY.CONTROLLER, tblSENEIncidentLogCY.CWS, tblSENEIncidentLogCY.HoaxMale, tblSENEIncidentLogCY.HoaxChild, tblSENEIncidentLogCY.HoaxTimeofCall, tblSENEIncidentLogCY.HoaxDate, tblSENEIncidentLogCY.TextofHoaxCall, tblSENEIncidentLogCY.HoaxWarwick, tblSENEIncidentLogCY.[HoaxNew Shoreham], tblSENEIncidentLogCY.[HoaxMarthas Vineyard], tblSENEIncidentLogCY.HoaxMiacomet, tblSENEIncidentLogCY.HoaxOrleans, tblSENEIncidentLogCY.[HoaxSignal Hill], tblSENEIncidentLogCY.HoaxTruro, tblSENEIncidentLogCY.FileName FROM tblSENEIncidentLogCY WHERE (((tblSENEIncidentLogCY.HoaxMale)=[Forms]![frmHoaxQuery]![child])); "Daryl S" wrote: Steph - Create a new form - no record source for the form. Add the checkboxes with appropriate descriptions and control names. Then in your query, in the criteria row below each field, add the value from the form. You can do this using the 'build' button in query design and navigate to the form and proper checkbox. These will look something like this: Forms!NewFormName.[cbxFirstYesNo] Where you would substitute the name of your form for NewFormName, and the name of the first check box (not the field name, but the name of the checkbox) for cbxFirstYes. These will work like parameters if the form is not open in form mode, so you can test your query by entering True or False while the form is in design mode or closed. If that isn't enough to get you started, repost with a description of what you have and where you need the help. -- Daryl S "Steph" wrote: Hi Daryl, thanks for the response. I love that idea, which will definitely get me my desired goal (even better). I am pretty comfortable with forms coding and queries, but do not know how to do what you mentioned. can you get me started? Thanks again! "Daryl S" wrote: Steph - You could build a form that has the criteria checkboxes on them, and whatever other criteria you need. Then when someone clicks on the query, bring up this form, and then run the query based on the values in the form's checkboxes. -- Daryl S "Steph" wrote: Hi All, I need a little expert advice here. I have a query that pulls from just one table. What I would like is once the query is clicked, have a series of questions, prob 3 or so to drive the criteria. In the past I have done this for dates but cant figure this one out. the fields to be queried are all checkboxes. IE: Was the voice Male or Female?(they are both checkboxes) Was it a child? (a checkbox) Which Antennae was it heard on? (6 total options, all checkboxes. thanks so much in advance!! Steph |
#4
|
|||
|
|||
question driven query
Steph -
The result will depend on how you write your query, and if you set default values on the form's checkboxes. The default would be the exact match, so if you only selected 'Night' as True, then the exact match would be all records where 'Night' is true and all other checkboxes are false. There is a trick that can use null checkboxes to indicate any value in the database is OK. You can test this out. In the query, put these two criteria on different lines for the 'Night' field (use your real form name): =(nz([Forms]![yourFormName]![cboNight],0)) =(nz([Forms]![yourFormName]![cboNight],-1)) This is basically saying if the checkbox is null, then allow a true or a false. If you have not set default values for the checkboxes on the form, then they will be 'null'. If you check them on then off, then they will be False, not null. If you want to set the 'False' values to 'Null', then you have to do that programatically, or by closing the form and re-opening it. -- Daryl S "Steph" wrote: Ok, figured out a way to ask a clearer question: If I have say 20 entries in my table with all of applicable fields checked off. Almost all of our calls are going to come at night. If I jsut check "Night" for my search criteria, will it return all Night ones? or do I need to fill out the exact match, if you will? thanks! "Steph" wrote: Ok, I got it! Question though. Does ALL of the criteria for a certain entry need to be made? IE: I have a call with Male, Night and Warwick all entered into the record. When I query Male, and Warwick, will both of those come back? I hope that makes sense. Thanks again! "Steph" wrote: Thanks so much daryl, you have me on the right track. a little snag. This is what I have: A form named frmHoaxQuery, (no record source) put in some check boxes with my criteria. my qry called qryProb_Hoax (the one I had before). In there. I have this under the MALE Voice field: [Forms]![frmHoaxQuery]![child] However it is returning nothing when i know there should be some. I think I am missing your instructions on the "yes" part! Here is the code of my query: Thanks so much! SELECT tblSENEIncidentLogCY.IncidentID, tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.incident_DTG, tblSENEIncidentLogCY.[Case Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS], tblSENEIncidentLogCY.CONTROLLER, tblSENEIncidentLogCY.CWS, tblSENEIncidentLogCY.HoaxMale, tblSENEIncidentLogCY.HoaxChild, tblSENEIncidentLogCY.HoaxTimeofCall, tblSENEIncidentLogCY.HoaxDate, tblSENEIncidentLogCY.TextofHoaxCall, tblSENEIncidentLogCY.HoaxWarwick, tblSENEIncidentLogCY.[HoaxNew Shoreham], tblSENEIncidentLogCY.[HoaxMarthas Vineyard], tblSENEIncidentLogCY.HoaxMiacomet, tblSENEIncidentLogCY.HoaxOrleans, tblSENEIncidentLogCY.[HoaxSignal Hill], tblSENEIncidentLogCY.HoaxTruro, tblSENEIncidentLogCY.FileName FROM tblSENEIncidentLogCY WHERE (((tblSENEIncidentLogCY.HoaxMale)=[Forms]![frmHoaxQuery]![child])); "Daryl S" wrote: Steph - Create a new form - no record source for the form. Add the checkboxes with appropriate descriptions and control names. Then in your query, in the criteria row below each field, add the value from the form. You can do this using the 'build' button in query design and navigate to the form and proper checkbox. These will look something like this: Forms!NewFormName.[cbxFirstYesNo] Where you would substitute the name of your form for NewFormName, and the name of the first check box (not the field name, but the name of the checkbox) for cbxFirstYes. These will work like parameters if the form is not open in form mode, so you can test your query by entering True or False while the form is in design mode or closed. If that isn't enough to get you started, repost with a description of what you have and where you need the help. -- Daryl S "Steph" wrote: Hi Daryl, thanks for the response. I love that idea, which will definitely get me my desired goal (even better). I am pretty comfortable with forms coding and queries, but do not know how to do what you mentioned. can you get me started? Thanks again! "Daryl S" wrote: Steph - You could build a form that has the criteria checkboxes on them, and whatever other criteria you need. Then when someone clicks on the query, bring up this form, and then run the query based on the values in the form's checkboxes. -- Daryl S "Steph" wrote: Hi All, I need a little expert advice here. I have a query that pulls from just one table. What I would like is once the query is clicked, have a series of questions, prob 3 or so to drive the criteria. In the past I have done this for dates but cant figure this one out. the fields to be queried are all checkboxes. IE: Was the voice Male or Female?(they are both checkboxes) Was it a child? (a checkbox) Which Antennae was it heard on? (6 total options, all checkboxes. thanks so much in advance!! Steph |
#5
|
|||
|
|||
question driven query
Daryl, thanks so much for all the help.
A couple questions, do I put your lines below on each control? I assume the answer is yes. Also, when I put my criteria in for the query, should I stagger them DOWN in each column? Thanks again! "Daryl S" wrote: Steph - The result will depend on how you write your query, and if you set default values on the form's checkboxes. The default would be the exact match, so if you only selected 'Night' as True, then the exact match would be all records where 'Night' is true and all other checkboxes are false. There is a trick that can use null checkboxes to indicate any value in the database is OK. You can test this out. In the query, put these two criteria on different lines for the 'Night' field (use your real form name): =(nz([Forms]![yourFormName]![cboNight],0)) =(nz([Forms]![yourFormName]![cboNight],-1)) This is basically saying if the checkbox is null, then allow a true or a false. If you have not set default values for the checkboxes on the form, then they will be 'null'. If you check them on then off, then they will be False, not null. If you want to set the 'False' values to 'Null', then you have to do that programatically, or by closing the form and re-opening it. -- Daryl S "Steph" wrote: Ok, figured out a way to ask a clearer question: If I have say 20 entries in my table with all of applicable fields checked off. Almost all of our calls are going to come at night. If I jsut check "Night" for my search criteria, will it return all Night ones? or do I need to fill out the exact match, if you will? thanks! "Steph" wrote: Ok, I got it! Question though. Does ALL of the criteria for a certain entry need to be made? IE: I have a call with Male, Night and Warwick all entered into the record. When I query Male, and Warwick, will both of those come back? I hope that makes sense. Thanks again! "Steph" wrote: Thanks so much daryl, you have me on the right track. a little snag. This is what I have: A form named frmHoaxQuery, (no record source) put in some check boxes with my criteria. my qry called qryProb_Hoax (the one I had before). In there. I have this under the MALE Voice field: [Forms]![frmHoaxQuery]![child] However it is returning nothing when i know there should be some. I think I am missing your instructions on the "yes" part! Here is the code of my query: Thanks so much! SELECT tblSENEIncidentLogCY.IncidentID, tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.incident_DTG, tblSENEIncidentLogCY.[Case Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS], tblSENEIncidentLogCY.CONTROLLER, tblSENEIncidentLogCY.CWS, tblSENEIncidentLogCY.HoaxMale, tblSENEIncidentLogCY.HoaxChild, tblSENEIncidentLogCY.HoaxTimeofCall, tblSENEIncidentLogCY.HoaxDate, tblSENEIncidentLogCY.TextofHoaxCall, tblSENEIncidentLogCY.HoaxWarwick, tblSENEIncidentLogCY.[HoaxNew Shoreham], tblSENEIncidentLogCY.[HoaxMarthas Vineyard], tblSENEIncidentLogCY.HoaxMiacomet, tblSENEIncidentLogCY.HoaxOrleans, tblSENEIncidentLogCY.[HoaxSignal Hill], tblSENEIncidentLogCY.HoaxTruro, tblSENEIncidentLogCY.FileName FROM tblSENEIncidentLogCY WHERE (((tblSENEIncidentLogCY.HoaxMale)=[Forms]![frmHoaxQuery]![child])); "Daryl S" wrote: Steph - Create a new form - no record source for the form. Add the checkboxes with appropriate descriptions and control names. Then in your query, in the criteria row below each field, add the value from the form. You can do this using the 'build' button in query design and navigate to the form and proper checkbox. These will look something like this: Forms!NewFormName.[cbxFirstYesNo] Where you would substitute the name of your form for NewFormName, and the name of the first check box (not the field name, but the name of the checkbox) for cbxFirstYes. These will work like parameters if the form is not open in form mode, so you can test your query by entering True or False while the form is in design mode or closed. If that isn't enough to get you started, repost with a description of what you have and where you need the help. -- Daryl S "Steph" wrote: Hi Daryl, thanks for the response. I love that idea, which will definitely get me my desired goal (even better). I am pretty comfortable with forms coding and queries, but do not know how to do what you mentioned. can you get me started? Thanks again! "Daryl S" wrote: Steph - You could build a form that has the criteria checkboxes on them, and whatever other criteria you need. Then when someone clicks on the query, bring up this form, and then run the query based on the values in the form's checkboxes. -- Daryl S "Steph" wrote: Hi All, I need a little expert advice here. I have a query that pulls from just one table. What I would like is once the query is clicked, have a series of questions, prob 3 or so to drive the criteria. In the past I have done this for dates but cant figure this one out. the fields to be queried are all checkboxes. IE: Was the voice Male or Female?(they are both checkboxes) Was it a child? (a checkbox) Which Antennae was it heard on? (6 total options, all checkboxes. thanks so much in advance!! Steph |
Thread Tools | |
Display Modes | |
|
|