Query Criteria issue

D

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
 
K

KARL DEWEY

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

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
Click to expand...

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.
 
D

Dave

Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Dave said:
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
Click to expand...

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

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
K

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.


Dave said:
Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Dave said:
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
Click to expand...

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

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Click to expand...
 
D

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

KARL DEWEY said:
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.


Dave said:
Thanks to both of you but neither solution worked.

Meaning they returned NO data.

Any ideas?

Thanks

Dave

Dave said:
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
Click to expand...

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

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Click to expand...

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

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Click to expand...

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

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query Formatting 3
Age calculation in Query 6
Query Criteria Not Working 2
Query Criteria 5
Complex Criteria Parameter Query 2
DO you think........... 4
Word Pages 1
IIF in a report Footer 3

Top