Re: Using Checkboxes with Parameter Queries
From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 08/07/04
- Next message: Jeff: "using a string instead of a textbox"
- Previous message: M.L. Sco Scofield: "Re: max of sum"
- In reply to: Jon: "Using Checkboxes with Parameter Queries"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 07 Aug 2004 16:36:25 +1200
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 Jon wrote: > 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 >
- Next message: Jeff: "using a string instead of a textbox"
- Previous message: M.L. Sco Scofield: "Re: max of sum"
- In reply to: Jon: "Using Checkboxes with Parameter Queries"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
