Using Checkboxes with Parameter Queries

[Jon]

I am trying to create a parameter query that connects to
a form that uses checkboxes as the method for selecting
records of individuals based on the sports that each
person plays. For instance, the form would consist of
three fields: Golf, football, and basketball (all
checkboxes).

The companion Parameter Query is based on a
table, "PersonSports," with these three fields plus the
person's ID number as the primary key. So the
table consists of 4 fields all together: PersonID, Golf,
Football, and Basketball. In the criterion section for
Golf, there is the expression [Forms]![PersonSearchForm]!
[Golf], indicating that the query should look to
the "Golf" field of the "PersonSearch" form for the value
to be used as the filter for pulling records. For the
other two fields I have the same expression except the
last part is altered to the appropriate field.

On the "PersonSearchForm" form, I check the golf checkbox
and leave the other checkboxes blank so as to pull up all
records for individuals who play golf. However, the
query returns only one record, and there is more than one
record with the value golf in the golf field. I know
that I have made a mistake in setting up the query and
form.

Does anyone know how to use checkboxes on forms that
connect to parameter queries? Ideally, I would like to
search for records using multiple fields, i.e. by
checking off football and golf and retrieving all
records that include these values, or by having an option
where selecting football and golf pulls up only those
records where a person plays both of these sports.

Sorry for the long message!!!

-Jon

 

[Steve Schapel]

Jon,

Well, this isn't really a Parameter Query, in that you are referencing
the criteria from a form control.

The way you have described it, if you tick the Golf checkbox on the
PersonSearchForm form, and the Football and Basketball checkboxes are
unticked, then I would expect the query to return all people who play
Golf, but do not play Football or Basketball. Is this not what you are
getting? Can you post back with the SQL view of the query? (You can
see this by selecting SQL from the View menu in design view of the query).

If you wanted to have each checkbox on the PersonSearchForm form result
in the players of each selected sport, regardless of whether or not they
played another one, you would need to put the criteria for each onto
separate criteria lines on the query design grid.

Having said this, I guess this sort of approach is manageable with only
3 sports, but with the addition of more sports to the database, it will
very quickly become unweildy. One of the main reasons for using a
programme like Access is to manage related data in a relational way. To
do this, you would re-design, so you would not have a separate field for
each sport. Instead, you would have a separate table, and each persons
participation in each sport would be a separate record in this table.
In the end, this approach would be much simpler.

--
Steve Schapel, Microsoft Access MVP

I am trying to create a parameter query that connects to
a form that uses checkboxes as the method for selecting
records of individuals based on the sports that each
person plays. For instance, the form would consist of
three fields: Golf, football, and basketball (all
checkboxes).

The companion Parameter Query is based on a
table, "PersonSports," with these three fields plus the
person's ID number as the primary key. So the
table consists of 4 fields all together: PersonID, Golf,
Football, and Basketball. In the criterion section for
Golf, there is the expression [Forms]![PersonSearchForm]!
[Golf], indicating that the query should look to
the "Golf" field of the "PersonSearch" form for the value
to be used as the filter for pulling records. For the
other two fields I have the same expression except the
last part is altered to the appropriate field.

On the "PersonSearchForm" form, I check the golf checkbox
and leave the other checkboxes blank so as to pull up all
records for individuals who play golf. However, the
query returns only one record, and there is more than one
record with the value golf in the golf field. I know
that I have made a mistake in setting up the query and
form.

Does anyone know how to use checkboxes on forms that
connect to parameter queries? Ideally, I would like to
search for records using multiple fields, i.e. by
checking off football and golf and retrieving all
records that include these values, or by having an option
where selecting football and golf pulls up only those
records where a person plays both of these sports.

Sorry for the long message!!!

-Jon