|
| 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
November 22nd, 2009, 06:54 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Evaluate Yes/No Field Based on User Input
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! 
|
|
#2
November 22nd, 2009, 08:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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 "Dana F. Brewer" wrote: 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!
|
|
#3
November 22nd, 2009, 09:01 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
Dana F. Brewer wrote:
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 -- Marsh MVP [MS Access]
|
|
#4
November 23rd, 2009, 06:40 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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!!! "Duane Hookom" wrote: 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 "Dana F. Brewer" wrote: 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!
|
|
#5
November 23rd, 2009, 01:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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 Dana F. Brewer wrote: 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!!! "Duane Hookom" wrote: 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 "Dana F. Brewer" wrote: 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!
|
|
#6
November 23rd, 2009, 08:33 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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? "John Spencer" wrote: 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 Dana F. Brewer wrote: 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!!! "Duane Hookom" wrote: 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 "Dana F. Brewer" wrote: 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! .
|
|
#7
November 23rd, 2009, 10:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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 "John Spencer" wrote: 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 Dana F. Brewer wrote: 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!!! "Duane Hookom" wrote: 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 "Dana F. Brewer" wrote: 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! .
|
|
#8
November 24th, 2009, 03:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Evaluate Yes/No Field Based on User Input
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 Dana F. Brewer wrote: 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 "John Spencer" wrote: 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 Dana F. Brewer wrote: 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!!! "Duane Hookom" wrote: 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 "Dana F. Brewer" wrote: 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! .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
