Checkboxes on Forms Connected to 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

 

[Michel Walsh]

Hi,


Filtering for a check box should be the same as for any other fields, unless
is it grouped, framed, and you selected the frame (which, acting as a group,
will return 1, 2, 3, .... rather than true or false).


Filtering occurs by record. If you select two sports, PracticeSport IN(
sport1, sport2) as example, filter one record at a time, so you get a OR,
ie, you get Joe is EITHER Joe practices sport1, either sport2. Asking for
people practicing BOTH sports is structurally different, since it requires a
check BETWEEN RECORDS. Simple filtering occurs for one record at a time,
independently,so, you have to use something else than a (simple) WHERE
clause, something else than filtering. Something that works is to first
filter, like usual, then, GROUP BY people, and COUNT the number of sport
THEN displayable (after filtering). You want to keep those HAVING that COUNT
= the number of sports you specified:

SELECT People
FROM PracticingSport
WHERE Sport IN( 'golf', 'football')
GROUP BY People
HAVING COUNT(*) = 2


Automating that query is doable, but that is a little bit complex. I suggest
you rather try to "write it at run time", supplying the IN list and the
comparison value for the COUNT, here 2 because we used two sports, as it
fit. Note that the WHERE clause is applied first, then the GROUP and COUNT
occur, then the HAVING take place. Since the COUNT occurred after the WHERE
is to be done, using:

WHERE COUNT(*)=2

is an error! the count has not occurred yet, so the WHERE can't handle it.
That is why, logically, such a construction is generally to be moved in the
HAVING clause.


Hoping it may help,
Vanderghast, 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