Form to filter report

[gcarpenter]

I have a form with two option groups on the form and a command button to apply the filter. The form then opens the report based on this filter, I cannot get the filter to apply both option group choices to the report when it open, it only applies one option group choice.

Code in form module:
Private Sub ApplyFilter_Click()
Select Case Me.ChooseYear.VALUE
Case 1
Me.Filter = "year = 2013"
Me.FilterOn = True
Case 2
Me.Filter = "year = 2014"
Me.FilterOn = True
Case 3
Me.Filter = "year = 2015"
Me.FilterOn = True
End Select

Select Case Me.ChooseMonth.VALUE
Case 1
Me.Filter = "Month = 1"
Me.FilterOn = True
Case 2
Me.Filter = "Month = 2"
Me.FilterOn = True
Case 3
Me.Filter = "Month = 3"
Me.FilterOn = True
Case 4
Me.Filter = "Month = 4"
Me.FilterOn = True
Case 5
Me.Filter = "Month = 5"
Me.FilterOn = True
Case 6
Me.Filter = "Month = 6"
Me.FilterOn = True
Case 7
Me.Filter = "Month = 7"
Me.FilterOn = True
Case 8
Me.Filter = "Month = 8"
Me.FilterOn = True
Case 9
Me.Filter = "Month = 9"
Me.FilterOn = True
Case 10
Me.Filter = "Month = 10"
Me.FilterOn = True
Case 11
Me.Filter = "Month = 11"
Me.FilterOn = True
Case 12
Me.Filter = "Month = 12"
Me.FilterOn = True
End Select
If Me.Filter = "" Then
MsgBox "No selection made"
Else
DoCmd.OpenReport "AllShipmentCriteria", acViewReport, , Me.Filter
'Me.FilterOn = True
End If
DoCmd.Close acForm, "AllDateFilter" 'then apply the filter

End Sub
:banghead:

 

[CJ_London]

1. filteron applies in the current form, not the report so is not required and me.filter is setting the current form filter - Dim a string called FilterStr
2. you can simplify your month to simply
FilterStr = "Month = " & ChooseMonth - no case statement required

Note that I have used the code tags and indented the code, this makes it much easier to read - many responders will ignore your post if it is difficult to read
try

Private Sub ApplyFilter_Click()
Dim FilterStr as String

    Select Case ChooseYear
        Case 1
            FilterStr= "year = 2013 AND Month = " & ChooseMonth"
        Case 2
            FilterStr= "year = 2014 AND Month = " & ChooseMonth"
        Case 3
            FilterStr = "year = 2015 AND Month = " & ChooseMonth"
    End Select

    If FilterStr= "" Then
        MsgBox "No selection made"
    Else
        DoCmd.OpenReport "AllShipmentCriteria", acViewReport, , FilterStr
    End If

    DoCmd.Close acForm, "AllDateFilter" 'then apply the filter

End Sub

Note that Year and Month are reserved words so using them can and will cause unexpected errors

Note also, you need fields called Year and Month(or their replacement) in your report

 

[gcarpenter]

Worked like a champ, that for the code tags tips as well.

 

[gcarpenter]

Sorry this did not work after all, I get syntax error at Filterstr in case statement.

 

[kevlray]

FYI: I see an odd number of double-quotes for each line.
"year = 2013 AND Month = " & ChooseMonth"

 

[CJ_London]

my bad - there should not be a double quote at the end

 

[gcarpenter]

Worked perfect, thank you so much.