Report Filters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am setting up a expense account database and want to create several reports
that are based on my Expense Table (I.E. Reconciled, Recimbursed, By Date,
etc), but I don't want to have to create separate reports/queries for each
report I want.

Is there a way that I can set up a Pop Up Form that I can use to filter my
report based on my table and or query?

thanks,
Brook
 
I think I have the same question so am hoping you get an answer to yours. I
want to do ad hoc filters by form and then run reports on the filtered sets.
It seems like it should be simple but ....

Peg
 
A belated thanks to DuncanG for his sample Q&A database. It wasn't quite
what I had in mind, however. The functionality I want is so basic, simple
and straight forward that I can't believe that it's not "built" into Access.
In Lotus Approach, I could just chose a report design option that limited the
records appearing in a report to the most recently selected subset of the
table. Do I truly need to use an applet (re: the Duane Hook message) or to
come up with some complicated macro employee an advanced filter???? At the
moment I'm filtering by form, cutting and pasting the ad hoc forms filter
properties into the record filter properties. Truly UGLY! Again, am I
missing something I should have picked up in Access 101?
 
The sample from the fontstuff website I believe was authored by Martin
Green. His stuff is very good.

The applet that I suggested is a good method for allowing users with very
little knowledge of the tables and relationships to create and run queries.
It is not designed to be used as the record source of a published report. I
generally include this applet in most applications that I create. It doesn't
take long to train users on how to use this and most like to push the
records to Excel where they can supply their own formatting.
 
Thanks Duane for publishing your tool. Although I need something where my
users can create queries and somewhat formatted reports on the fly without
going through the DB window, your tool will be a handy study item. I need to
audit activity in the DB, so i am trying to keep everyone working through
forms.
 
I used the code from this website and it works fine unless there are null
values in one or more of the fields I'm using in the filter. If one of the
fields is null for a particular record, and the other fields in that record
meet the chosen criteria, I want the record to be returned in the report. In
other words, the I'm trying for is something like: if Cateogry = "cosmetics"
AND (Region like * OR Region is Null), then....But I don't know how to
translate this into the correct syntax for the filter statement. Can anyone
help with the code I would need to add to ignore the nulls?
Thank you!

Here's my current code:

Private Sub CmdApplyCriteria_Click()
Dim strCategory As String
Dim strRegion As String
Dim strReportYr As String
Dim strFilter As String

If IsNull(Me.cboCategory.Value) Then
strCategory = "Like '*'"
Else
strCategory = "='" & Me.cboCategory.Value & "'"
End If

If IsNull(Me.cboRegion.Value) Then
strRegion = "Like '*' "
Else
strRegion = "='" & Me.cboRegion.Value & "'"
End If

If IsNull(Me.cboReportYr.Value) Then
strReportYr = "Like '*' "
Else
strReportYr = "='" & Me.cboReportYr.Value & "'"
End If

strFilter = "[Category] " & strCategory & " AND [Region] " & strRegion & "
AND [ReportYr] " & strReportYr

With Reports![Product Report]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 
I'm not sure about the context of the code but you can add "" to a string to
make it match Like "*". For instance:

strFilter = "[Category] & '' " & strCategory & " AND [Region] & '' " &
strRegion & "
AND [ReportYr] " & strReportYr

Note the pair of single quotes concatenated to the two field names.

--
Duane Hookom
MS Access MVP
--

Wendy said:
I used the code from this website and it works fine unless there are null
values in one or more of the fields I'm using in the filter. If one of the
fields is null for a particular record, and the other fields in that
record
meet the chosen criteria, I want the record to be returned in the report.
In
other words, the I'm trying for is something like: if Cateogry =
"cosmetics"
AND (Region like * OR Region is Null), then....But I don't know how to
translate this into the correct syntax for the filter statement. Can
anyone
help with the code I would need to add to ignore the nulls?
Thank you!

Here's my current code:

Private Sub CmdApplyCriteria_Click()
Dim strCategory As String
Dim strRegion As String
Dim strReportYr As String
Dim strFilter As String

If IsNull(Me.cboCategory.Value) Then
strCategory = "Like '*'"
Else
strCategory = "='" & Me.cboCategory.Value & "'"
End If

If IsNull(Me.cboRegion.Value) Then
strRegion = "Like '*' "
Else
strRegion = "='" & Me.cboRegion.Value & "'"
End If

If IsNull(Me.cboReportYr.Value) Then
strReportYr = "Like '*' "
Else
strReportYr = "='" & Me.cboReportYr.Value & "'"
End If

strFilter = "[Category] " & strCategory & " AND [Region] " & strRegion & "
AND [ReportYr] " & strReportYr

With Reports![Product Report]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
 
Back
Top