More than one parameter

[ampickel]

I set up a database to keep track of athletes enrolled in a soccer camp. The
table is
structured with first and last names, address, basically all information
reguarding the athlete. I have two colums, one for male and one for female,
both are yes/no fields, (check boxes), and two columns, one for day camp and
one for high school camp. What I want to do is run a query so that I can
pull records in any combination, for example all male day campers, or all
female high school campers or all high school campers male and female. I
can do one parameter just fine and I know the null option to get both, but
when I add
the other parameter my queries come up blank. Any ideas!

 

[pietlinden]

I set up a database to keep track of athletes enrolled in a soccer camp. The
table is
structured with first and last names, address, basically all information
reguarding the athlete.  I have two colums, one for male and one for female,
both are yes/no fields, (check boxes), and two columns, one for day camp and
one for high school camp.  What I want to do is run a query so that I can
pull records in any combination, for example all male day campers, or all
female high school campers or all high school campers male and female.  I
can do one parameter just fine and I know the null option to get both, but
when I add
the other parameter my queries come up blank.  Any ideas!

Bad design.
You should have ONE column for gender, and if the day/highschool
options are mutually exclusive, those should be a single column too.
That's why querying is such a hassle.

 

[ampickel]

Ok, I went in and set up one column and changed all the records. But when I
run the query as null for one of the parameters it still comes up blank.
Example. prompt comes up male or female. I choose null and leave it blank,
then next prompt is Day camp or High school camp, I pick Day camp and it is
blank. The only time I get records to pop up is when I choose Male or Female,
and Day camp or High school camp. I can't do the null. Why?

 

[Pete D.]

If the data isn't null then nothing will come up. Depends on design of
your query so just out of a guess try * instead of male/female for all.

 

[ampickel]

My understanding of the Null value is this. In the design view under
Criteria I put [Male or Female] to be prompted. Then in the or box below it
I put [Male or Female] Is Null, if I want it to return all records. Is that
correct? Then when the query runs I just leave the prompt box blank and all
records will show. But when I do that for one parameter it like, ignores the
other parameter. Example if I want both male and female Day campers. I put
in the propmpt boxes, nothing for the first and Day camp for the second and I
get no records returned. Is there a way to run both?

 

[Bob Barrows [MVP]]

Because the query will only return results where the field value equals the
parameter value. Nothing equal Null, so nothing gets returned.

What you need to do is something like this (switch your query to SQL View to
see the sql statement):

WHERE ... ([Gender] = [Male or Female] Or [Male or Female] is Null) ...

 

[Pete D.]

Just so you know, Null is the absents of any value so if it is Male or
Female it isn't Null. Only an unchanged blank field would = Null

My understanding of the Null value is this. In the design view under
Criteria I put [Male or Female] to be prompted. Then in the or box below
it
I put [Male or Female] Is Null, if I want it to return all records. Is
that
correct? Then when the query runs I just leave the prompt box blank and
all
records will show. But when I do that for one parameter it like, ignores
the
other parameter. Example if I want both male and female Day campers. I
put
in the propmpt boxes, nothing for the first and Day camp for the second
and I
get no records returned. Is there a way to run both?

If the data isn't null then nothing will come up. Depends on design of
your query so just out of a guess try * instead of male/female for all.