Query Criteria issue

[Dave]

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named "cboGender"
When the user makes a selection "Male" a report is generated for only Males.
But in some cases the user will want to run a report for both (all) genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show information
on Both (all) genders.

So in the query that generates the report, in the criteria for the Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes


My logic was that if there was nothing in the criteria ("") it would return
all genders.

Didn't work.

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several Demographic
fields with drop downs and I want the user to be able to report on ONE or
ALL for any of the fields.
Hope I am making sense.

Thanks

Dave



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5029 (20100414) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Marshall Barton]

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named "cboGender"
When the user makes a selection "Male" a report is generated for only Males.
But in some cases the user will want to run a report for both (all) genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show information
on Both (all) genders.

So in the query that generates the report, in the criteria for the Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes

My logic was that if there was nothing in the criteria ("") it would return
all genders.

Don't confuse the shorthand criteria that are allowed in the
query designer with what you need to use in the resulting
SQL statement. When you use an expression the a query
designer criteria, it must return a *value* that can be
compared to the field. You can not generate part of an
expression.

In this case, it's easier to switch to SQL view and directly
edit the WHERE clause instead of going through the
contortions required to get the designer to generate a messy
equivalent expression. Try using a WHERE clause like:

WHERE Forms!frmStudentDemographics!ckAllGender
OR gender = Forms!frmStudentDemographics!cboGender

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several Demographic
fields with drop downs and I want the user to be able to report on ONE or
ALL for any of the fields.

A good idea to use the OpenForm/OpenReport's WhereCondition
argument instead of making a mess of the query to get it to
ignore optional criteria. For an example, see the article at
http://allenbrowne.com/ser-62.html

 

[KARL DEWEY]

Try this --
Like IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"*",
[forms]![frmStudentDemographics]![cboGender])

 

[fredg]

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named "cboGender"
When the user makes a selection "Male" a report is generated for only Males.
But in some cases the user will want to run a report for both (all) genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show information
on Both (all) genders.

So in the query that generates the report, in the criteria for the Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes

My logic was that if there was nothing in the criteria ("") it would return
all genders.

Didn't work.

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several Demographic
fields with drop downs and I want the user to be able to report on ONE or
ALL for any of the fields.
Hope I am making sense.

Thanks

Dave


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5029 (20100414) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

As there are only 2 genders, male or female, I would suggest you
simply add the word "All" to the combo box list.
Set the Combo RowSourceType property to Value List. Set it's RowSource
to "All";"Female";"Male" (Change the value order to whatever sort
order you wish).

Then change the query Where clause to:

Like IIf([forms]![frmStudentDemographics]![cboGender] = "All","*",
[forms]![frmStudentDemographics]![cboGender])

The user need either select "All" in the combo box or the gender
wanted.

If you are were using a combo rowsource derived from a query or table,
then use a Union query to add the word "All" to the list.

Change the table and field names to your actual table and field names.

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

First add "All" to the drop down list.
NOTE: The below code will need to be adapted to whatever your specific
needs are. The below field and table names are generic.

If there are, for example, 2 fields to be included in the combo
rowsource, as Combo box Rowsource:

Select tblOrderStatus.OrderStatusID,OrderStatus From tblOrderStatus
Union Select Null, "<ALL>" from tblOrderStatus Order by OrderStatus;

The above shows the OrderStatusID as well as the OrderStatus fields,
leaving a blank in the OrderStatusID field where <All> is shown.

<All>
15 OptionA
3 OptionB
20 etc...

Or¡K Do you wish to include just one column in the RowSource?

Select tblOrderStatus.OrderStatus From tblOrderStatus Union Select
"<ALL>" from tblOrderStatus Order by OrderStatus;

<All>
OptionA
OptionB
etc...

Then if you are using the OrderStatus to filter data on,
change the query criteria to:

Like
IIf([forms]![FormName]![ComboName]="<ALL>","*",[forms]![FormName]![ComboName])

The form must be open when the query/report is run.

 

[Dave]

Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named
"cboGender"
When the user makes a selection "Male" a report is generated for only
Males.
But in some cases the user will want to run a report for both (all)
genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show
information on Both (all) genders.

So in the query that generates the report, in the criteria for the Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes


My logic was that if there was nothing in the criteria ("") it would
return all genders.

Didn't work.

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several Demographic
fields with drop downs and I want the user to be able to report on ONE or
ALL for any of the fields.
Hope I am making sense.

Thanks

Dave



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5029 (20100414) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[KARL DEWEY]

A couple of questions.
What is the DataType of your Gender field? Post sample of data directly
from the table.

What is the source of data of your cboGender?

--
Build a little, test a little.

Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named
"cboGender"
When the user makes a selection "Male" a report is generated for only
Males.
But in some cases the user will want to run a report for both (all)
genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show
information on Both (all) genders.

So in the query that generates the report, in the criteria for the Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes


My logic was that if there was nothing in the criteria ("") it would
return all genders.

Didn't work.

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several Demographic
fields with drop downs and I want the user to be able to report on ONE or
ALL for any of the fields.
Hope I am making sense.

Thanks

Dave



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5029 (20100414) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Dave]

Gender fields is a value list - not pulling from a table.
All;Male;Female

I think it is not liking the "*"

If I remove the * and put in Female in the query criteria it gives me
correct answer

Thanks

Dave

A couple of questions.
What is the DataType of your Gender field? Post sample of data directly
from the table.

What is the source of data of your cboGender?

--
Build a little, test a little.

Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Access 2003:

Trying to create a demographics report that will allow the following.

Gender example
On the form "frmStudentDemographics" is a drop down field named
"cboGender"
When the user makes a selection "Male" a report is generated for only
Males.
But in some cases the user will want to run a report for both (all)
genders.
So I added a check box next to the drop down named "ckAllGender"
My idea is that if that box is checked then the report will show
information on Both (all) genders.

So in the query that generates the report, in the criteria for the
Gender
field I tried this:

IIf([forms]![frmStudentDemographics]![ckAllGender]=True,"",
[forms]![frmStudentDemographics]![cboGender])

Note - I also tried: = 1, = yes


My logic was that if there was nothing in the criteria ("") it would
return all genders.

Didn't work.

Any suggestions on how to rewrite my criteria
OR
A different approach to this problem

I am just using gender as an example as there will be several
Demographic
fields with drop downs and I want the user to be able to report on ONE
or
ALL for any of the fields.
Hope I am making sense.

Thanks

Dave



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5029 (20100414) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5030 (20100415) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5033 (20100416) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5033 (20100416) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com