Using check boxes to set criteria

[Tara]

I'm hoping someone can give me some help with check
boxes. What I need is some info on using them to
determine whether or not to include certain data in a
query. For example, I have a query that pulls info
regarding the amount of time a family has received
services through our agency. I may or may not want to
include everyone based on the type of service and the
county they reside in. For example, this month I want to
run this query and include everyone in programs A and B
from counties 1, 2, and 3. Next month I want to run the
same query and include those in program A from Counties
1, 2, 4 and 5. My plan was to have check boxes a main
form to set the criteria. Any ideas on how this can be
done?

 

[Michel Walsh]

Hi,


Check box are terrible since if you add one additional info (a new
country, a new program) you have to TOUCH the physical design of the form in
order to add it there too, so, the end client have to have access to the
code... and modifying code in a month or in a year is not necessary the best
thing to do. A list, on the other hand, expand as the data in the table
supporting the list expand, without having to touch the design of the form,
neither the code. A multiple selection list allows you to select more than
one row (keep the control key down, click on the individual row of the
list). To use the selected items of a list in a query, take a look at
http://www.mvps.org/access/forms/frm0007.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Jeff]

Wow I have this exact same issue!! I would love to use
the solution below but I have no idea what a multiple
selection list box or how to create one. Could someone
please explain this and how to use the code mentioned.

Also since my other form is created is there still a way
to do this via checkboxes even though it is not the best
way to do it.

Thanks
jeff dot savage at comcast dot net

 

[Michel Walsh]

Hi,



It is a standard list box, for which you assign the property MultiSelect
from the default "None" to "Simple" or "Extended".


As for check box are concerned, you have to manually loop through them,
one by one, unless they have a sequencing like:

Dim InList As String
For i = 25 To 33 Step 2
If Me("check" & i ).Value Then
InList=InList & ", " & Me("check" & i).Tag
End If
Next i

If 0<>len(InList) Then InList=" IN(" & Mid(InList, 3) & ")"

' Debug.Print InList ' to check if the list is well formed


assuming, here, you used the Tag property to specify the value, with the
right delimiter, to be checked against in the database (as example,
Check25.tag="""January""", to produce, debug.print, a list like:

IN("January", "February") .


Sure, using the tag that way make further addition even more complex: if you
add a check box, in three months, you have to remember to also specify its
"tag" appropriately. Not necessary a good idea.


Hoping it may help,
Vanderghast, Access MVP