Filter Report Data Using Multiple Option Groups 1

[colbertsm]

I would like for the user to filter the data on their report by making selections from 3 separate Option Groups: Year, Type, and Status.

I've set up a form with all three Option Groups on one tab control; then included a Preview command button to set the criteria and open the report in PrintPreview mode.

I need help on how to write the Where clause to include all three user selections - I can do any of the three, but only one at a time. Copy of Code:

Private Sub Preview_Click()

Dim strFilter1 As String
Dim strFilter2 As String
Dim strFilter3 As String

'Determine which year
Select Case Year
Case 1 '2009
strFilter1 = "DateRequested <= #12/31/2009#"
Case 2 '2010
strFilter1 = "DateRequested Between #01/01/2010# And #12/31/2010#"
End Select

'Determine which donation type
Select Case DonationType
Case 1 'Children
strFilter2 = "DonationType = 'Children'"
Case 2 'Community
strFilter2 = "DonationType = 'Community'"
Case 3 'Military
strFilter2 = "DonationType = 'Military'"
Case 4 'Other
strFilter2 = "DonationType = 'Other'"
Case 5 'United Way
strFilter2 = "DonationType = 'United Way'"
Case 6 'Veterans Memorial
strFilter2 = "DonationType = 'Veterans Memorial'"
End Select

'Determine status
Select Case Status
Case 1 'Pending
strFilter3 = "Status = 'P'"
Case 2 'Committed
strFilter3 = "Status = 'C'"
Case 3 'Paid
strFilter3 = "Status = 'Y'"
Case 4 'Denied
strFilter3 = "Status = 'N'"
End Select

DoCmd.OpenReport "rptUserDialog", acViewPreview, , strFilter1 [[or strFilter2, or strFilter3]]
DoCmd.Close acForm, "frmDonationTypeDialog"

End Sub

Many thanks in advance!

 

[lameid]

Build your whole filter programmatically...

Assuming the values defaulted to zero length strings...

I would write another function that Or's the criteria together... It should take two parameters, the existing criteria expression and the additional criteria expression.

If there exsiting expressing is zero length then return the additional criteria otherwise concatenate with the appropriate or.

I have done this before using and, below.

Function addcriteria(ByVal strExistingCriteria As String, ByVal strAdditionalCriteria As String) As String
    If Len(strExistingCriteria & "") = 0 Then
        addcriteria = strAdditionalCriteria
    Else
        addcriteria = "(" & strExistingCriteria & ")" & " and " & strAdditionalCriteria
    End If
End Function

 

[colbertsm]

lameid - thanks for pointing me in the right direction! Concatenating the criteria solved the problem - our users will never have an "or" situation as all three choices are required. Code that solved the issue:

strAddCriteria = "(" & strFilter1 & ")" & " and " & "(" & strFilter2 & ")" & " and " & "(" & strFilter3 & ")"

DoCmd.OpenReport "rptUserDialog", acViewPreview, , strAddCriteria

Many thanks!

 

[dhookom]

cobertsm,
That's typically the method that I prefer to use however I see lots of extra quotes etc in your code (this is common in these fora)

strAddCriteria = "(" & strFilter1 & ")" & " and " & "(" & strFilter2 & ")" & " and " & "(" & strFilter3 & ")"

could be:

strAddCriteria = "(" & strFilter1 & ") and (" & strFilter2 & ") and (" & strFilter3 & ")"

This saves several keystrokes that you might need later ;-)



Duane
Hook'D on Access
MS Access MVP

 

[colbertsm]

Duane - thanks again!