filter report based on filters in form

[favor08]

have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.


Any suggestions


This is what i have so far:

Dim StrWhere As String 
With Me.[FPastDuePendingsSub].Form 
           If Me.FPastDuePendingsSub.Form.FilterOn = True Then 
                StrWhere = Me.FPastDuePendingsSub.Form.Filter & _ 
        " AND ([opid] = """ & Me.cboopid & """)" 
           End If 
        End With 
DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

[ADezii]

have a mainform called PendingsMain and a subform called
PendingsSub. You can filter the subform by different filters and this
works fine.

i want to create a report that will print out the results of the users
filters or print everything if there is no filters submitted within
the form.


Any suggestions


This is what i have so far:


Dim StrWhere As String
With Me.[FPastDuePending sSub].Form
If Me.FPastDuePend ingsSub.Form.Fi lterOn = True Then
StrWhere = Me.FPastDuePend ingsSub.Form.Fi lter & _
" AND ([opid] = """ & Me.cboopid & """)"
End If
End With
DoCmd.OpenRepor t "RPendDetai l", acViewPreview, , StrWhere


if an "opid" is not selected but another filter is like "ProdCD"
"DateIn", "Policy" it will not filter the report. I need to take in
account all of the filters that could be selected or left null just
not sure how.

1. The first thing I did was to tidy up your code a litte since the current syntax defeats the purpose of the With..End With Construct.
2. I was a little confused by the question, but I think that the following revised code will point you in the right direction.
   [CODE=vb]Dim StrWhere As String
   With Me.[FPastDuePending sSub].Form
   If .FilterOn = True Then
   If Not IsNull(Me.cpoop id) Then
   StrWhere = .Filter & " AND ([opid] = """ & Me.cboopid & """)"
   Else
   StrWhere = .Filter
   End If
   End If
   End With
   
   DoCmd.OpenRepor t "RPendDetai l", acViewPreview, , StrWhere[/CODE]

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

MODERATOR.

[favor08]

1. The first thing I did was to tidy up your code a litte since the current syntax defeats the purpose of the With..End With Construct.
2. I was a little confused by the question, but I think that the following revised code will point you in the right direction.
   [CODE=vb]Dim StrWhere As String
   With Me.[FPastDuePending sSub].Form
   If .FilterOn = True Then
   If Not IsNull(Me.cpoop id) Then
   StrWhere = .Filter & " AND ([opid] = """ & Me.cboopid & """)"
   Else
   StrWhere = .Filter
   End If
   End If
   End With
   
   DoCmd.OpenRepor t "RPendDetai l", acViewPreview, , StrWhere[/CODE]

so do I continue to add iff then statements for the other filters?

[favor08]

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
  If .FilterOn = True Then
    If Not IsNull(Me.CboOpid) Then
      StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
    Else
      StrWhere = .Filter
      End If
      If Not IsNull(Me.cbofilterProd) Then
      StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd & """)"
      Else
      StrWhere = .Filter
          End If
          If Not IsNull(Me.cboCmtCd) Then
          StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd & """)"
          Else
          StrWhere = .Filter
          End If
   End If
End With
 DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

this is what I have everytime I add a piece of code the previous filter does not work. If no filter is picked the report works and if I pick a excd it works but now the opid and prodcd does not work. If I remoe the excd filter the prodcd will work but not the opid and so on.

[ADezii]

Dim StrWhere As String
With Me.[FPastDuePendingsSub].Form
  If .FilterOn = True Then
    If Not IsNull(Me.CboOpid) Then
      StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
    Else
      StrWhere = .Filter
      End If
      If Not IsNull(Me.cbofilterProd) Then
      StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd & """)"
      Else
      StrWhere = .Filter
          End If
          If Not IsNull(Me.cboCmtCd) Then
          StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd & """)"
          Else
          StrWhere = .Filter
          End If
   End If
End With
 DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere

this is what I have everytime I add a piece of code the previous filter does not work. If no filter is picked the report works and if I pick a excd it works but now the opid and prodcd does not work. If I remoe the excd filter the prodcd will work but not the opid and so on.

The effects of multiple Filters does not seem to be cumulative, e.g. Filter #2 does not work off Filter #1.
You may have to change your strategy:

1. Declare StrWhere as a Public Variable.
2. Instead of building a Filter String, build a SQL String using the same StrWhere Variable.
3. In the Open() Event of your Report set the RecordSource to something similar to:
   [CODE=sql]Me.RecordSource = "SELECT * FROM <your table> WHERE " & StrWhere & ";"[/CODE]