question driven query

[Steph]

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

 

[Daryl S]

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 -

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.

 

[Steph]

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]));

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

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]

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

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]));

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

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!

:

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


:

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

 

[Daryl S]

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

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!

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!

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]));






:

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


:

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!

:

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


:

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

 

[Steph]

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!

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

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!

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!

:

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]));






:

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


:

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!

:

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


:

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