How to create a query that will evaluate multiple check boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Lets say I have a table which holds a persons name and then up to five
further Yes/No fields which describe the time of the day they might like to
watch TV

I have a form that has a check box for each of the times, so check
1=8am-10am, check 2=10am-noon etc

Now I can create a query that simply returns the people who like to watch tv
based upon check 1 but the complexity arises when I want the user to be able
to interact with all five check boxes on the form.

When I offer more than 1 check box on the form and link it to the criteria
section of the query access insists on evaluating the check boxes that are
left clear. I want to be able to only query on those check boxes I care about
which may not be all five.

To elaborate further if I selected check 1 3 and 5 but left 2 and 4 blank
access would only return those people who have checked 1 3 and 5 but also Not
checked 2 and 4 and I want to be able to ignore 2 and 4 in certain
situations. Can I design a form/query combination that allows me to do this?

Any advice appreciated.
 
assuming that the checkbox controls on the form are unbound (the
ControlSource property of each control is blank), try the following:

set up the criteria as OR references, not AND references. to do this, put
the reference on the *first* criteria line of the first Yes/No field. then
move over to the next Yes/No field, and put the reference criteria on the
*second* criteria line - leaving the first line blank. then move over to the
next Yes/No field, and put the reference criteria on the third criteria
line - leaving the first and second lines blank. continue in this fashion,
"stair-stepping" the reference criteria, until you've added criteria for
each Yes/No field tat refers to the corresponding checkbox on the form.

add a command button on the form, to run the query. add code to the button's
Click event procedure, as

DoCmd.OpenQuery "NameOfQuery"
With Me
!chkTime1 = Null
!chkTime2 = Null
!chkTime3 = Null
!chkTime4 = Null
End With

substitute the correct name of the query and the correct names of the
checkbox controls on the form, of course (adding or deleting lines of code
as needed, to handle all the checkbox controls on your form). the above code
opens the query, obviously; if you want to open a form or report that's
bound to the query (usually preferable to opening a query directly), then
change the Open command accordingly.

note that i'm not endorsing your non-normalized table design, and i do
recommend that you consider normalizing your table structure before you move
forward. you'd have to change the form design to work with a normalized
table, but IMHO that's an an acceptable trade-off for a correctly designed
structure.

hth
 
Tina

Thanks very much for your advice.

Using your suggestions I am a lot closer to the solution now.

The example I gave doesnt really reflect the structure, I just wanted to
make it strauight forward. Thanks for you help and advice.

Regards

Tony.
 
you're welcome :)


Tony Deady said:
Tina

Thanks very much for your advice.

Using your suggestions I am a lot closer to the solution now.

The example I gave doesnt really reflect the structure, I just wanted to
make it strauight forward. Thanks for you help and advice.

Regards

Tony.
Click to expand...
 
Back
Top