424,054 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Filter Criteria in a Report

P: 2
I am trying to display the "Filter by Form" criteria in a Report (that displays the filtered information). I have an unbound textbox with [Forms]![RosterForm].[Filter] as the control source that will give me the Filter itself but I need to clean this up. I envisioned a listing in the header of the report that would list each of the categories (there are six) and the filter criteria specified for the category.

This is a demographics database application for a Fire Department. My criteria are Sex (Male, Female), Race (American Indian/Alaska Native, Asian, Black/African-American, Hispanic, White/Caucasian, Other), Rank (Chief, Deputy Chief, Battalion Chief, Captain, Master Firefighter, Firefighter, Civilian), Paramedic Status (Paramedic, Non-Paramedic), and Assignment(Operations, Administration).

I wanted to have labels in the header and the filter criteria for each category. For example:

Assignment: Operations
Assignment: Administration
Assignment: Operations & Administration
Assignment: Not Specified

Can this be done? This is an example of my filter string:

((([Roster Query].Rank)="3") AND (([Roster Query].Assignment)="1") AND (([Roster Query].Sex)="1") AND (([Roster Query].Paramedic)="1") AND (([Roster Query].Race)="6")) OR ((([Roster Query].Sex)="2"))
Nov 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: 2
Actually, for the string that I provided, the header would appear like this:

Rank: Battalion Chief
Sex: Male
Paramedic Status: Paramedic
Race: White/Caucasian
Sex: Male

((([Roster Query].Rank)="3") AND (([Roster Query].Assignment)="1") AND (([Roster Query].Sex)="1") AND (([Roster Query].Paramedic)="1") AND (([Roster Query].Race)="6")) OR ((([Roster Query].Sex)="2"))[/quote]
Nov 22 '06 #2

NeoPa
Expert Mod 15k+
P: 31,170
In fact, yes there is.
I will copy in an example of something I've done along those lines.
Bear in mind, this was not designed for your purpose and is only included to prompt you with ideas of some of the things that can be done.

Bear in mind that setting the report's RecordSource property will cause the underlying dataset to be re-calculated.
The help system should explain which properties are available to you.

Above all, don't be daunted by the length of the code sample, it has various concepts within but you're not likely to need all. Treat it as a 'reference library'. Also bear in mind some of the code may refer to items (procedures, tables, etc)which are not available to you.
[code]'On Open, this report determines the state of the form and adjusts various
'things based on that. If form not found then it defaults to showing :
' Main Stock Products only
' Costs
' Opal Column Prices
' Sorting must be handled by multiple reports.
Private Sub Report_Open(Cancel As Integer)
Dim intSort As Integer, intSelRange As Integer, intPCs As Integer
Dim intSpare As Integer
Dim strSort As String, strWork As String, strType As String
Dim strPC1 As String, strPC2 As String
Dim blnReplacement As Boolean, blnCosts As Boolean, blnVisible As Boolean

On Error Resume Next
'If next line fails then intFrom stays 0 - otherwise it will be > 0
intFrom = Forms("frm" & conStub).fraFrom
On Error GoTo 0
intPCs = 0
If intFrom = 0 Then
'Form not found
intSelRange = 1
blnReplacement = True
blnCosts = True
intSort = 2
strSort = "Prod Group then Desc"
intFrom = 3
intPCs = 1
dblPC1 = 100 / 60 '40% GPM
Else
'Form found - use settings from form
With Forms("frm" & conStub)
intSelRange = IIf(.chkMainStock, 1, 0) + IIf(.chkLawForms, 2, 0)
blnReplacement = .chkReplacement
blnCosts = .chkShowCost
intSort = .fraSort
Select Case intSort
Case 1
strSort = .lblPGrpCode.Caption
Case 2
strSort = .lblPGrpDesc.Caption
Case 3
strSort = .lblProdCode.Caption
Case 4
strSort = .lblProdDesc.Caption
End Select
If intFrom > 1 Then
intPCs = 1
strPC1 = Nz(.txtPC1, "")
dblPC1 = IIf(intFrom = 2, (100 + CDbl(strPC1)) / 100, _
100 / (100 - CDbl(strPC1)))
strPC2 = Nz(.txtPC2, "")
dblPC2 = CDbl(0)
If strPC2 > "" Then
intPCs = 2
dblPC2 = IIf(intFrom = 2, (100 + CDbl(strPC2)) / 100, _
100 / (100 - CDbl(strPC2)))
End If
End If
End With
End If
'Set up Sorting & Grouping
'Only show PGroupHdr if sorting primarily by PGroup and turn triggering
'to as few as possible (using Prefix) if not showing (otherwise Each)
'Assume both levels must be set with different fields regardless
PGroupHdr.Visible = (intSort < 3)
'.GroupOn ==> 0 = Each; 1 = Prefix
GroupLevel(0).GroupOn = IIf(intSort < 3, 0, 1)
Select Case intSort
Case 1
GroupLevel(0).ControlSource = "PGroup"
GroupLevel(1).ControlSource = "Product"
Case 1
GroupLevel(0).ControlSource = "PGroup"
GroupLevel(1).ControlSource = "ProdDesc"
Case 3
GroupLevel(0).ControlSource = "Product"
GroupLevel(1).ControlSource = "PGroup"
Case 4
GroupLevel(0).ControlSource = "ProdDesc"
GroupLevel(1).ControlSource = "PGroup"
End Select
With txtTitle
Select Case intFrom
Case 1
strType = "Columns"
Case 2
strType = "Markups"
Case 3
strType = "GPMs"
End Select
.ControlSource = ParamReplace(.ControlSource, "%S", strSort, _
"%T", strType)
End With
If intSelRange = 3 Then
FilterOn = False
Else
FilterOn = True
strWork = Split(Expression:=conFilters, Delimiter:="|")(intSelRange - 1)
Filter = ParamReplace(strWork, "%L", conLawForms)
End If
lblCost.Visible = blnCosts
txtCost.Visible = blnCosts
'Adjust widths and visibilities of various fields
'Some field attribute may need to be adjusted from their starting values
'ProdDesc.Width = 7.354cm (4,170) conDescWidth
'Unit.Width = 1.24cm (703) conUnitWidth
'PriceFields.Width = 1.199cm (680) conPriceWidth
'Expand width of Description if either Columns OR Cost not shown
lblProdDesc.Width = _
conDescWidth + IIf(intFrom > 1 Or Not blnCosts, lblCost.Width, 0)
'Set basic widths for Price Columns 1 - 2
lblPrice1.Width = conPriceWidth
lblPrice2.Width = conPriceWidth
'Set captions, width and visibility for Column fields used
If intPCs < 1 Then
lblPrice2.Caption = ParamReplace(conColLbl, "%N", 2)
lblPrice3.Caption = ParamReplace(conColLbl, "%N", 3)
txtPrice2.ControlSource = "Price2"
txtPrice3.ControlSource = "Price3"
Else
strWork = strType & vbCrLf
lblPrice2.Caption = strWork & strPC1 & "%"
txtPrice2.ControlSource = "=Round([Cost]*" & dblPC1 & ",2)"
lblPrice3.Caption = strWork & strPC2 & "%"
txtPrice3.ControlSource = "=Round([Cost]*" & dblPC2 & ",2)"
End If
lblPrice3.Visible = (intPCs > 1)
'Special handling for new style report (chkReplacement = True)
If blnReplacement Then
lblPrice2.Caption = ParamReplace("Min Sell%L%N%", "%N", strPC1, _
"%L", vbCrLf)
lblReplacement.Visible = True
End If
'Handle last 3 column fields (Only leave required fields visible)
blnVisible = (intFrom = 1)
lblPrice4.Visible = blnVisible
lblPrice5.Visible = blnVisible
lblPrice6.Visible = blnVisible
txtPrice4.Visible = blnVisible
txtPrice5.Visible = blnVisible
txtPrice6.Visible = blnVisible
'Build up positions and mirror widths & visibilities
intSpare = (Width - BuildUp() - 10) / 4
If intFrom > 1 And intSpare > 25 Then
If intSpare > conExtend Then intSpare = conExtend
lblUnit.Width = conUnitWidth + intSpare
lblCost.Width = conPriceWidth + intSpare
lblPrice1.Width = conPriceWidth + intSpare
lblPrice2.Width = conPriceWidth + intSpare
Call BuildUp
End If
'Set up Report ID & date for bottom left corner of report
strDate = DLookup(Expr:="
Expand|Select|Wrap|Line Numbers
  1. ", _
  2.                       Domain:="[tblReport]", _
  3.                       Criteria:="[ReportName]='" & Name & "'") & _
  4.                                 Format(Date, " - d mmmm yyyy")
  5. End Sub
Nov 22 '06 #3

Post your reply

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