Evaluate Yes/No Field Based on User Input

[Dana F. Brewer]

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

 

[Duane Hookom]

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

 

[Marshall Barton]

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no.

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus.

You can prompt users for the Yes or No by setting the Yes/No
field's criteria to something like:

=([Enter Yes or No] = "Yes")

But, to answer the bonus question, you should create a form
with a check box and a command button to run the query (or
whatever you are doing with it). In this case the query
criteria would look like:
=Forms!theform.thecheckbox

 

[Dana F. Brewer]

Thanks Duane. However, I need to be able to get only Active = Yes or
otherwise return ALL records. In this case if the user types Yes, they do
get all active = yes and if they type no they get all active = no. I don't
want either/or. I would like to get YES or ALL.

Is there a way to modify the sample you gave me to get the result I need?

Thanks a bunch!!!

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

--
Duane Hookom
Microsoft Access MVP

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

 

[John Spencer]

WHERE ([Active]= [Enter Y for Yes]="Y" OR Nz([Enter Y for Yes],"N")<>"Y")

In query design view you would have columns that looked like:
Field: [Active]
Criteria(Line 1): [Enter Y for Yes]="Y"
Criteria(Line 2): <<Leave Blank>>


Field: Expr1: Nz([Enter Y for Yes],"N")
Criteria(Line 1): [<<Leave Blank>>
Criteria(Line 2): <>"Y"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane. However, I need to be able to get only Active = Yes or
otherwise return ALL records. In this case if the user types Yes, they do
get all active = yes and if they type no they get all active = no. I don't
want either/or. I would like to get YES or ALL.

Is there a way to modify the sample you gave me to get the result I need?

Thanks a bunch!!!

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

--
Duane Hookom
Microsoft Access MVP

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

 

[Dana F. Brewer]

Hi John. Thanks for your help. As soon as I saw your suggested I knew I was
headed in the write direction. In fact, I used exactly what you suggested
here (cut and paste) except that I put the actual field name in place of
[active]. Field name is tblterritory.[active?]. However, I got an error:

"This expression is typed incorrectly, or is too complex to be evaluated."

Don't mean to whine but can you help me find the problem?

WHERE ([Active]= [Enter Y for Yes]="Y" OR Nz([Enter Y for Yes],"N")<>"Y")

In query design view you would have columns that looked like:
Field: [Active]
Criteria(Line 1): [Enter Y for Yes]="Y"
Criteria(Line 2): <<Leave Blank>>


Field: Expr1: Nz([Enter Y for Yes],"N")
Criteria(Line 1): [<<Leave Blank>>
Criteria(Line 2): <>"Y"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane. However, I need to be able to get only Active = Yes or
otherwise return ALL records. In this case if the user types Yes, they do
get all active = yes and if they type no they get all active = no. I don't
want either/or. I would like to get YES or ALL.

Is there a way to modify the sample you gave me to get the result I need?

Thanks a bunch!!!

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

--
Duane Hookom
Microsoft Access MVP


:

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

.

 

[Dana F. Brewer]

By the way, when I enter Y or N I get the error. When I enter nothing or
something other than Y or N I get all records. Thanks again in advance if you
or someone is able to help me straighten this out.

I promise I will take the time to think about using a form in the future but
getting this piece solved now will help me tremendously.

....Dana

WHERE ([Active]= [Enter Y for Yes]="Y" OR Nz([Enter Y for Yes],"N")<>"Y")

In query design view you would have columns that looked like:
Field: [Active]
Criteria(Line 1): [Enter Y for Yes]="Y"
Criteria(Line 2): <<Leave Blank>>


Field: Expr1: Nz([Enter Y for Yes],"N")
Criteria(Line 1): [<<Leave Blank>>
Criteria(Line 2): <>"Y"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane. However, I need to be able to get only Active = Yes or
otherwise return ALL records. In this case if the user types Yes, they do
get all active = yes and if they type no they get all active = no. I don't
want either/or. I would like to get YES or ALL.

Is there a way to modify the sample you gave me to get the result I need?

Thanks a bunch!!!

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

--
Duane Hookom
Microsoft Access MVP


:

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

.

 

[John Spencer]

Try

In query design view you would have columns that looked like:
Field: [Active]
Criteria(Line 1): IIF([Enter Y for Yes]="Y",True,False)
Criteria(Line 2): <<Leave Blank>>


Field: Expr1: Nz([Enter Y for Yes],"N")
Criteria(Line 1): <<Leave Blank>>
Criteria(Line 2): <>"Y"

If that does not work then I suggest you post the SQL statement you are using.
Hint - Menu View: SQL, copy and paste the text.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

By the way, when I enter Y or N I get the error. When I enter nothing or
something other than Y or N I get all records. Thanks again in advance if you
or someone is able to help me straighten this out.

I promise I will take the time to think about using a form in the future but
getting this piece solved now will help me tremendously.

...Dana

WHERE ([Active]= [Enter Y for Yes]="Y" OR Nz([Enter Y for Yes],"N")<>"Y")

In query design view you would have columns that looked like:
Field: [Active]
Criteria(Line 1): [Enter Y for Yes]="Y"
Criteria(Line 2): <<Leave Blank>>


Field: Expr1: Nz([Enter Y for Yes],"N")
Criteria(Line 1): [<<Leave Blank>>
Criteria(Line 2): <>"Y"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane. However, I need to be able to get only Active = Yes or
otherwise return ALL records. In this case if the user types Yes, they do
get all active = yes and if they type no they get all active = no. I don't
want either/or. I would like to get YES or ALL.

Is there a way to modify the sample you gave me to get the result I need?

Thanks a bunch!!!

:

A Yes/No field actually stores -1 for yes or 0 for no. A simple parameter
prompt query could use a criteria under the field like:
[Enter Y for Yes]="Y"
I always feel it is much better to provide controls on forms for all user
interaction. For instance if you had a check box "chkMyCB" on a form
"frmMyForm". Your criteria would be:
Forms!frmMyForm!chkMyCB

--
Duane Hookom
Microsoft Access MVP


:

Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?

Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.

Thanks!

.