Event Procedure Owned vs Not Owned and by Month

[Hedwig14]

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim strCriteria As String
Dim strHeading As String
Select Case Me.lstReportTypes
Case 0 ' Owned
strCriteria = "[Owned]"
strHeading = "Owned"
Case 1 ' not Owned
strCriteria = "Not [Owned]"
strHeading = "Not Owned"
End Select
Which then opens my report...I didn't include the entire event as you get
the idea. I want to be able to base my report on not only owned vs not owned
but on a month criteria. I have built 2 queries that include the main table
in one field, Expr1: Format([IncorporationDate],"mmmm") criteria [Enter
Month], and Owned with criteria of Yes in the 3rd field. The other query is
the same but for Owned the criteria is No. I have only ever built a form
with one command such as above and don't know how to do the second task. I
hope I am making myself clear.

 

[Graham Mandeno]

Hi Hedwig

You can build up very complex filter strings in VBA before applying them to
a form or an OpenReport method:

For example:

Select Case Me.lstReportTypes
Case 0 ' Owned
strCriteria = "[Owned]<>0"
strHeading = "Owned"
Case 1 ' not Owned
strCriteria = "[Owned]=0"
strHeading = "Not Owned"
End Select
If Not IsNull(cboSelectMonth) Then
If Len(strCriteria)<>0 Then strCriteria = strCriteria & " AND "
strCriteria = strCriteria & 'Format([IncorporationDate],'mmmm')='" _
& cboSelectMonth & "'"
If Len(strHeading)<>0 Then strHeading= strHeading & "/"
strHeading = strHeading & "Incorporated during " & cboSelectMonth
End If
....
DoCmd.OpenReport ...