By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

filter report based on filters in form

P: 56
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:


Expand|Select|Wrap|Line Numbers
  1. Dim StrWhere As String 
  2. With Me.[FPastDuePendingsSub].Form 
  3.            If Me.FPastDuePendingsSub.Form.FilterOn = True Then 
  4.                 StrWhere = Me.FPastDuePendingsSub.Form.Filter & _ 
  5.         " AND ([opid] = """ & Me.cboopid & """)" 
  6.            End If 
  7.         End With 
  8. 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.
May 27 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,627
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.
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim StrWhere As String
    2. With Me.[FPastDuePendingsSub].Form
    3.   If .FilterOn = True Then
    4.     If Not IsNull(Me.cpoopid) Then
    5.       StrWhere = .Filter & " AND ([opid] = """ & Me.cboopid & """)"
    6.     Else
    7.       StrWhere = .Filter
    8.     End If
    9.   End If
    10. End With
    11.  
    12. DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere
May 27 '07 #2

NeoPa
Expert Mod 15k+
P: 31,433
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.
May 27 '07 #3

P: 56
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim StrWhere As String
    2. With Me.[FPastDuePendingsSub].Form
    3.   If .FilterOn = True Then
    4.     If Not IsNull(Me.cpoopid) Then
    5.       StrWhere = .Filter & " AND ([opid] = """ & Me.cboopid & """)"
    6.     Else
    7.       StrWhere = .Filter
    8.     End If
    9.   End If
    10. End With
    11.  
    12. DoCmd.OpenReport "RPendDetail", acViewPreview, , StrWhere
so do I continue to add iff then statements for the other filters?
May 27 '07 #4

P: 56
Expand|Select|Wrap|Line Numbers
  1. Dim StrWhere As String
  2. With Me.[FPastDuePendingsSub].Form
  3.   If .FilterOn = True Then
  4.     If Not IsNull(Me.CboOpid) Then
  5.       StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
  6.     Else
  7.       StrWhere = .Filter
  8.       End If
  9.       If Not IsNull(Me.cbofilterProd) Then
  10.       StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd & """)"
  11.       Else
  12.       StrWhere = .Filter
  13.           End If
  14.           If Not IsNull(Me.cboCmtCd) Then
  15.           StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd & """)"
  16.           Else
  17.           StrWhere = .Filter
  18.           End If
  19.    End If
  20. End With
  21.  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.
May 27 '07 #5

ADezii
Expert 5K+
P: 8,627
Expand|Select|Wrap|Line Numbers
  1. Dim StrWhere As String
  2. With Me.[FPastDuePendingsSub].Form
  3.   If .FilterOn = True Then
  4.     If Not IsNull(Me.CboOpid) Then
  5.       StrWhere = .Filter & " AND ([opid] = """ & Me.CboOpid & """)"
  6.     Else
  7.       StrWhere = .Filter
  8.       End If
  9.       If Not IsNull(Me.cbofilterProd) Then
  10.       StrWhere = .Filter & " AND ([prodcd] = """ & Me.cbofilterProd & """)"
  11.       Else
  12.       StrWhere = .Filter
  13.           End If
  14.           If Not IsNull(Me.cboCmtCd) Then
  15.           StrWhere = .Filter & " And ([excd] = """ & Me.cboCmtCd & """)"
  16.           Else
  17.           StrWhere = .Filter
  18.           End If
  19.    End If
  20. End With
  21.  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:
    Expand|Select|Wrap|Line Numbers
    1. Me.RecordSource = "SELECT * FROM <your table> WHERE " & StrWhere & ";"
May 28 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.