Criteria for Query

[Martin]

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.

 

[KARL DEWEY]

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))

 

[Martin]

I tried your suggestion and have the same results. The Yes and No parameters
work, but the "All" parameter does not.

Thanks.

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))
--
KARL DEWEY
Build a little - Test a little

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.

 

[KARL DEWEY]

It worked for me. Post your query SQL.
Open query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

I tried your suggestion and have the same results. The Yes and No parameters
work, but the "All" parameter does not.

Thanks.

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))
--
KARL DEWEY
Build a little - Test a little

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.

 

[Martin]

Here is the SQL. If it matters, this Y/N field is one of 3 criteria that the
user enters from a form. Also, I am running Access 2002.

I am in the process of trying to see if changing the field to a text field,
rather than Yes/No will help. I doubt it, but...

========
SELECT DISTINCTROW tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]) AS
QACode, Count(CStr([QuestCode]) & CStr([AnswerCode])) AS QACount
FROM tblDirectors INNER JOIN (tblTargetAreas INNER JOIN tblSurvey ON
tblTargetAreas.TargetNo = tblSurvey.SurveyArea) ON tblDirectors.DirectorID =
tblTargetAreas.TargetDirector
GROUP BY tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode])
HAVING (((tblDirectors.DirectorGoal)=IIf([Forms].[Count Report
Criteria].[GoalSelect]="Yes",-1,IIf([Forms].[Count Report
Criteria].[GoalSelect]="No",0,"*"))) AND
((tblSurvey.SurveyQuarter)=[Forms].[Count Report Criteria].[USurvQtr]) AND
((tblSurvey.SurveyYear)=[Forms].[Count Report Criteria].[USurvYr]) AND
((tblSurvey.QuestCode)<99))
ORDER BY tblSurvey.QuestCode, tblSurvey.AnswerCode;
========

It worked for me. Post your query SQL.
Open query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

I tried your suggestion and have the same results. The Yes and No parameters
work, but the "All" parameter does not.

Thanks.

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))
--
KARL DEWEY
Build a little - Test a little


:

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.

 

[KARL DEWEY]

The pasting wrapped and lost 'Like' out of it.
Make it read thus ---
HAVING (((tblDirectors.DirectorGoal) Like IIf([Forms].[Count Report
.......

--
KARL DEWEY
Build a little - Test a little

Here is the SQL. If it matters, this Y/N field is one of 3 criteria that the
user enters from a form. Also, I am running Access 2002.

I am in the process of trying to see if changing the field to a text field,
rather than Yes/No will help. I doubt it, but...

========
SELECT DISTINCTROW tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]) AS
QACode, Count(CStr([QuestCode]) & CStr([AnswerCode])) AS QACount
FROM tblDirectors INNER JOIN (tblTargetAreas INNER JOIN tblSurvey ON
tblTargetAreas.TargetNo = tblSurvey.SurveyArea) ON tblDirectors.DirectorID =
tblTargetAreas.TargetDirector
GROUP BY tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode])
HAVING (((tblDirectors.DirectorGoal)=IIf([Forms].[Count Report
Criteria].[GoalSelect]="Yes",-1,IIf([Forms].[Count Report
Criteria].[GoalSelect]="No",0,"*"))) AND
((tblSurvey.SurveyQuarter)=[Forms].[Count Report Criteria].[USurvQtr]) AND
((tblSurvey.SurveyYear)=[Forms].[Count Report Criteria].[USurvYr]) AND
((tblSurvey.QuestCode)<99))
ORDER BY tblSurvey.QuestCode, tblSurvey.AnswerCode;
========

It worked for me. Post your query SQL.
Open query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

I tried your suggestion and have the same results. The Yes and No parameters
work, but the "All" parameter does not.

Thanks.


:

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))
--
KARL DEWEY
Build a little - Test a little


:

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.

 

[Martin]

My apologies. When I first read your suggestion, I thought you meant the
"Like" to be as in "for example", not that it was the Like Operator. I now
see what you meant.

Thanks for the help.

The pasting wrapped and lost 'Like' out of it.
Make it read thus ---
HAVING (((tblDirectors.DirectorGoal) Like IIf([Forms].[Count Report
......

--
KARL DEWEY
Build a little - Test a little

Here is the SQL. If it matters, this Y/N field is one of 3 criteria that the
user enters from a form. Also, I am running Access 2002.

I am in the process of trying to see if changing the field to a text field,
rather than Yes/No will help. I doubt it, but...

========
SELECT DISTINCTROW tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]) AS
QACode, Count(CStr([QuestCode]) & CStr([AnswerCode])) AS QACount
FROM tblDirectors INNER JOIN (tblTargetAreas INNER JOIN tblSurvey ON
tblTargetAreas.TargetNo = tblSurvey.SurveyArea) ON tblDirectors.DirectorID =
tblTargetAreas.TargetDirector
GROUP BY tblSurvey.SurveyArea, tblDirectors.DirectorGoal,
tblSurvey.SurveyQuarter, tblSurvey.SurveyYear, tblSurvey.QuestCode,
tblSurvey.AnswerCode, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode])
HAVING (((tblDirectors.DirectorGoal)=IIf([Forms].[Count Report
Criteria].[GoalSelect]="Yes",-1,IIf([Forms].[Count Report
Criteria].[GoalSelect]="No",0,"*"))) AND
((tblSurvey.SurveyQuarter)=[Forms].[Count Report Criteria].[USurvQtr]) AND
((tblSurvey.SurveyYear)=[Forms].[Count Report Criteria].[USurvYr]) AND
((tblSurvey.QuestCode)<99))
ORDER BY tblSurvey.QuestCode, tblSurvey.AnswerCode;
========

It worked for me. Post your query SQL.
Open query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I tried your suggestion and have the same results. The Yes and No parameters
work, but the "All" parameter does not.

Thanks.


:

There a lot of ways to do this but a simple way is to use a listbox on you
form with Yes, No, and N/A or Both.
In your query design view criteria enter this --
Like
IIf([Forms]![YourFormName]![LostBox]="Yes",-1,IIf([Forms]![YourFormName]![LostBox]="No",0,"*"))
--
KARL DEWEY
Build a little - Test a little


:

I am working with a database that uses a form to allow the user to select
three criteria for a query which is used in a report. One of the criteria is
a field containing either a Y or N. The user has been able to select either
Y or N to generate the report with the selected records, but now they want to
be able to select both, to get all records.

As far as I can tell, the controls from the form are used as the criteria in
the query design view. The problem I am having is what to enter into the
form to allow the query to select all records for that Y/N field.

I have tried coding vbNull into the control, but that does not work. Simply
leaving the control blank does not work (I assume that is the same as null).
I have tried coding "Y or N" into the control since that is what the SQL
looks like, but that does not work.

Is there a way to do what is needed? The only option I see now is to
duplicate the query (and possibly the report) to eliminate the need for the
Y/N field critieria.

Thanks.