Combining multiple inclusion conditions in Report Printing Macro

C

Carol

I have a mailing label form which prompts the user to check one or
more of 6 inclusion checkboxes (member types)and one option box with 2
additional inclusion options (all -or- only those with no email
address). What is the best way to deal with programming the variety
of inclusion conditions.
Thanks in advance,
Carol
 
S

Steve Schapel

Carol,

One relatively easy way to do this...
Make a simple lookup table (if you haven't already got one) for
MemberTypes, with a Yes/No field, let's call it Included. Make a
continuous view form based on this table, and add it to your mailing
label setup form as a subform, in place of your existing checkboxes.
Then, add this table to the query that your mailing labels is based on,
joined to your Member table on the MemberType field, and inclide the
Included field in the query. Then, all you need to do is put -1 in the
criteria of the Included field in the query. And then, in the criteria
of the EmailAddress field in the query, put the equivalent of...
Is Not Null Or [Forms]![NameOfForm]![NameOfOptionGroup]=1
 
C

Carol

Steve:
Thanks for responding. This is what I have tried. My thought was
that for every checked box, I would create a string value to put in an
IN statement in the doCmd.OpenReport statement. It seems like it
should work. But no.
Sorry I haven't commented this piece of code - it is a click event on
the form with the checkboxes.
Thanks in advance,
Carol


Private Sub Print_Mailing_Labels_Click()


Dim strActive As String
Dim strHonorary As String
Dim strStudent As String
Dim strProspect As String
Dim strDualFamily As String
Dim strFriend As String
Dim strHonoraryActive As String


If Me.Active = -1 Then strActive = "Active"
If Me.Active_Honorary = -1 Then strHonoraryActive = "Honorary
Active"
If Me.Student = -1 Then strStudent = "Student"
If Me.Dual_Family = -1 Then strDualFamily = "Dual Family"
If Me.Prospect = -1 Then strProspect = "Prospect"
If Me.Friend = -1 Then strFriend = "Friend"

Debug.Print strActive


DoCmd.OpenReport "Avery Mailing Labels (5260)", acViewPreview, ,
"[Avery Mailing Labels (5260)]![Member Type] IN
(strActive,strHonoraryActive,strStudent, strDualFamily,
strProspect,strFriend)"

End Sub


Steve Schapel said:
Carol,

One relatively easy way to do this...
Make a simple lookup table (if you haven't already got one) for
MemberTypes, with a Yes/No field, let's call it Included. Make a
continuous view form based on this table, and add it to your mailing
label setup form as a subform, in place of your existing checkboxes.
Then, add this table to the query that your mailing labels is based on,
joined to your Member table on the MemberType field, and inclide the
Included field in the query. Then, all you need to do is put -1 in the
criteria of the Included field in the query. And then, in the criteria
of the EmailAddress field in the query, put the equivalent of...
Is Not Null Or [Forms]![NameOfForm]![NameOfOptionGroup]=1

--
Steve Schapel, Microsoft Access MVP

I have a mailing label form which prompts the user to check one or
more of 6 inclusion checkboxes (member types)and one option box with 2
additional inclusion options (all -or- only those with no email
address). What is the best way to deal with programming the variety
of inclusion conditions.
Thanks in advance,
Carol
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top