Enabling Users to Filter Report Results

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have several reports that given various information about our
contstruction projects. I have a table "tblProjectStatus" with the following
fields:

prjStatusID prjStatusDesc
1 Feasibility
2 Design
3 Construction
4 Closeout
5 On Hold
6 Completed
7 Cancelled

All of the reports I want to filter have the Status field in their control
source. I want to create some way that a user can say on Monday let me see
these reports for all projects with status 2 and 3. Then on Tuesday maybe
they want to see these reports for status 5, 6 and 7.

I have absolutely no idea how to accomplish that. Any suggestions or
direction is greatly appreciated and valued. THANKS!
 
Stacey said:
Hi. I have several reports that given various information about our
contstruction projects. I have a table "tblProjectStatus" with the following
fields:

prjStatusID prjStatusDesc
1 Feasibility
2 Design
3 Construction
4 Closeout
5 On Hold
6 Completed
7 Cancelled

All of the reports I want to filter have the Status field in their control
source. I want to create some way that a user can say on Monday let me see
these reports for all projects with status 2 and 3. Then on Tuesday maybe
they want to see these reports for status 5, 6 and 7.
Click to expand...


First. you should use a form where users can specify the
criteria and one or more buttons to open reports with the
specified filter.

In your case I suggest that you use a multi select list box
for users to select the desired statuses. Then the button
code would follow the lines of this article:
http://allenbrowne.com/ser-50.html
 
Thank you Marsh. I tried to follow the directions and subsititute my field
names. I think I am close. I have two issues now.

1. error 3464 - Data type mismatch in expression
2. my list box only allows one selction. how do I select more than one item?

Here is the code:
Private Sub cmdViewBudgets_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptBudgetsCombined"

With Me.listprjStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[pstStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
 
Stacey said:
I tried to follow the directions and subsititute my field
names. I think I am close. I have two issues now.

1. error 3464 - Data type mismatch in expression
2. my list box only allows one selction. how do I select more than one item?

Here is the code:
Private Sub cmdViewBudgets_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptBudgetsCombined"

With Me.listprjStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[pstStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Click to expand...


In the form's design view, double click on the list box.
Then look down the property list for the Multi Select
property and choose either Simple or Extended according to
your desired UI. Once this is done, users can select one or
more items in the list. Then the code can be used to build
a WhereCondition for the OpenReport method.

Your code presumes that pstStatusID is a number type field
in its table. Please confirm this is the case.

If the field is a number type field, then there is no need
for the strDelim variable, but maybe there is a future
reason for leaving the code the way it is.
 
Ok. I fixed the list properties to enable multi-select. My prjStatusID
field is a text field. I am not familiar enough with visual basic to edit
the code to account for it being a text field. What part do I need to change?

Thank you for helping me :)
Stacey

Marshall Barton said:
Stacey said:
I tried to follow the directions and subsititute my field
names. I think I am close. I have two issues now.

1. error 3464 - Data type mismatch in expression
2. my list box only allows one selction. how do I select more than one item?

Here is the code:
Private Sub cmdViewBudgets_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptBudgetsCombined"

With Me.listprjStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[pstStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Click to expand...


In the form's design view, double click on the list box.
Then look down the property list for the Multi Select
property and choose either Simple or Extended according to
your desired UI. Once this is done, users can select one or
more items in the list. Then the code can be used to build
a WhereCondition for the OpenReport method.

Your code presumes that pstStatusID is a number type field
in its table. Please confirm this is the case.

If the field is a number type field, then there is no need
for the strDelim variable, but maybe there is a future
reason for leaving the code the way it is.
Click to expand...
 
So, the need is immediate ;-)

Add the line:

strDelim = """" ' results in one double quote

right before the strDoc = ... line
--
Marsh
MVP [MS Access]


Stacey said:
Ok. I fixed the list properties to enable multi-select. My prjStatusID
field is a text field. I am not familiar enough with visual basic to edit
the code to account for it being a text field. What part do I need to change?


Marshall Barton said:
Stacey said:
I tried to follow the directions and subsititute my field
names. I think I am close. I have two issues now.

1. error 3464 - Data type mismatch in expression
2. my list box only allows one selction. how do I select more than one item?

Here is the code:
Private Sub cmdViewBudgets_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptBudgetsCombined"

With Me.listprjStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[pstStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Click to expand...


In the form's design view, double click on the list box.
Then look down the property list for the Multi Select
property and choose either Simple or Extended according to
your desired UI. Once this is done, users can select one or
more items in the list. Then the code can be used to build
a WhereCondition for the OpenReport method.

Your code presumes that pstStatusID is a number type field
in its table. Please confirm this is the case.

If the field is a number type field, then there is no need
for the strDelim variable, but maybe there is a future
reason for leaving the code the way it is.
Click to expand...
Click to expand...
 
Wow, I can't believe it works!!!!!!!!! YAY! I have another situation I'm
cooking up so I'll need help on that too. But I'll post a new message once I
get ready. Thanks Marsh & Allen Browne!

Marshall Barton said:
So, the need is immediate ;-)

Add the line:

strDelim = """" ' results in one double quote

right before the strDoc = ... line
--
Marsh
MVP [MS Access]


Stacey said:
Ok. I fixed the list properties to enable multi-select. My prjStatusID
field is a text field. I am not familiar enough with visual basic to edit
the code to account for it being a text field. What part do I need to change?


Marshall Barton said:
Stacey Crowhurst wrote:

I tried to follow the directions and subsititute my field
names. I think I am close. I have two issues now.

1. error 3464 - Data type mismatch in expression
2. my list box only allows one selction. how do I select more than one item?

Here is the code:
Private Sub cmdViewBudgets_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptBudgetsCombined"

With Me.listprjStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[pstStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


In the form's design view, double click on the list box.
Then look down the property list for the Multi Select
property and choose either Simple or Extended according to
your desired UI. Once this is done, users can select one or
more items in the list. Then the code can be used to build
a WhereCondition for the OpenReport method.

Your code presumes that pstStatusID is a number type field
in its table. Please confirm this is the case.

If the field is a number type field, then there is no need
for the strDelim variable, but maybe there is a future
reason for leaving the code the way it is.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top