Check Boxes as Report Criteria

[BruceF]

I have a form with 4 check boxes that represents high school grade levels.
They are labeled as 09, 10, 11, 12. Users can pick one, two, three, or all
four check boxes. Suppose they choose 11 and 12. Based on the selections of
these check boxes, I want a report to display all students who are in grades
11 and 12. The report is based on a total query that pulls the students' ID,
name, grade, and total number of demerits. Thanks.

 

[Allen Browne]

Add a command button to your form to open the report.
In its Click event procedure, build up the WhereConditon for OpenReport, so
it opens filtered to the grades you want.

This kind of thing:
Private Sub cmdPreview_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.chk09.Value Then
strWhere = "9, "
End If

If Me.chk10.Value Then
strWhere = strWhere & "10, "
End If

If Me.chk11.Value Then
strWhere = strWhere & "11, "
End If

If Me.chk12.Value Then
strWhere = strWhere & "12, "
End If

'remove trailing comma and space
lngLen = Len(strWhere) - 2
If lngLen > = Then
strWhere = "[Grade] IN (" & Left$(strWhere, lngLen) & ")"
End if

DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

 

[BruceF]

Allen,
You are wonderful. I had this huge IIF statement in my query and the report
ran for 30 or more seconds -- too long. Now, it runs in a few seconds. Thank
you so much!!
BruceF

Add a command button to your form to open the report.
In its Click event procedure, build up the WhereConditon for OpenReport, so
it opens filtered to the grades you want.

This kind of thing:
Private Sub cmdPreview_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.chk09.Value Then
strWhere = "9, "
End If

If Me.chk10.Value Then
strWhere = strWhere & "10, "
End If

If Me.chk11.Value Then
strWhere = strWhere & "11, "
End If

If Me.chk12.Value Then
strWhere = strWhere & "12, "
End If

'remove trailing comma and space
lngLen = Len(strWhere) - 2
If lngLen > = 1 Then
strWhere = "[Grade] IN (" & Left$(strWhere, lngLen) & ")"
End if

DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with 4 check boxes that represents high school grade levels.
They are labeled as 09, 10, 11, 12. Users can pick one, two, three, or all
four check boxes. Suppose they choose 11 and 12. Based on the selections
of
these check boxes, I want a report to display all students who are in
grades
11 and 12. The report is based on a total query that pulls the students'
ID,
name, grade, and total number of demerits. Thanks.