defining criteria for a report

  • Thread starter Thread starter todd
  • Start date Start date
T

todd

Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
 
I would base the report on a query and build an SQL string to put in the
where condition of the openreport code.

I would have a form with the following:
option groups::
1 for Priority - 3 options, All, Range, Single
1 for date range - 2 options All, Range (or 3 if you want a single date)
1 for Status - 2 options Single, All

I would then have textboxes/comboxbox that appear based on the selections
that you have made. eg. cbxPriortyFrom, cbxPriorityTo

A command button to open the report. The code to build SQL string would be
behind the OnClick event of the button. Somethings like:


dim strSQL as string

strSQL = ""
Select Case me.optPriority
case 1
case 2
strSQL = "Priority Between '" & me.cbxPriorityFrom & "' AND '"
me.cbxPriorityTo & "'"
case 3
strSQL = "Priority = '" & me.cbxPriorityFrom
end select

if strSQL <> "" then
strSQL = strSQL & " AND "
end if

select case me.optStatus
case 1
case 2
strSQL = strSQL & "Status = '" & me.txtStatus
end select

if strSQL <> "" then
strSQL = strSQL & " AND "
end if

select case me.optDate
case 1
case 2 etc etc
end select

DoCmd.OpenReport "reportname",acViewPreview,, strSQL


Hope this helps!
 
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date and ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from the non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case, it adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1) 'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut & ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date] >= " & _
Format(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & " And " & _
Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

todd said:
Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...
 
Allen,
Thanks for the help.
I tried to do what you said, and I started off small (ie
just trying to get the listbox for priority to work
first). I created the form with the priority listbox and
command button for the report.

When I click on the command button, I get prompted to
enter Priority, and I get nothing in the report.

What have I done wrong?

Here is the code behind the command button for the report
preview:

Private Sub ProjectListReportPreview_Click()
Dim lst As ListBox
Dim varItem As Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[PriorityList]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData
(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1)
'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut
& ")) AND "
End Select

'DATES
'If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then
'End, no start.
'strWhere = strWhere & "([Completion Date] <= "
& _
'Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
'End If
'Else
' If IsNull(Me.txtEndDate) Then
'Start, no end.
'strWhere = strWhere & "([Completion Date] >= "
& _
'Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' Else
'Both start and end.
' strWhere = strWhere & "([Completion Date]
Between " & _
' Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
' Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' End If
'End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Project Listing", acViewPreview, ,
strWhere

Set lst = Nothing
End Sub

-----Original Message-----
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date and ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from the non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case, it adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData (varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1) 'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut & ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date]
= " & _
Format
Click to expand...
(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & " And " & _
Format
Click to expand...
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...


.
Click to expand...
 
Allen,
Thanks for the help.
I tried to do what you said, and I started off small (ie
just trying to get the listbox for priority to work
first). I created the form with the priority listbox and
command button for the report.

When I click on the command button, I get prompted to
enter Priority, and I get nothing in the report.

What have I done wrong?

Here is the code behind the command button for the report
preview:

Private Sub ProjectListReportPreview_Click()
Dim lst As ListBox
Dim varItem As Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[PriorityList]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """
& .ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData
(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1)
'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut
& ")) AND "
End Select

'DATES
'If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then
'End, no start.
'strWhere = strWhere & "([Completion Date] <= "
& _
'Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
'End If
'Else
' If IsNull(Me.txtEndDate) Then
'Start, no end.
'strWhere = strWhere & "([Completion Date] >= "
& _
'Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' Else
'Both start and end.
' strWhere = strWhere & "([Completion Date]
Between " & _
' Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
' Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' End If
'End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Project Listing", acViewPreview, ,
strWhere

Set lst = Nothing
End Sub
-----Original Message-----
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date and ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from the non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case, it adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData (varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1) 'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut & ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date]
= " & _
Format
Click to expand...
(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & " And " & _
Format
Click to expand...
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...


.
Click to expand...
 
Presumably the report is based on a table or query that has a field named
"Priority"? If not, you won't be able to select the records where the
Priority field has one of the values in the list box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the help.
I tried to do what you said, and I started off small (ie
just trying to get the listbox for priority to work
first). I created the form with the priority listbox and
command button for the report.

When I click on the command button, I get prompted to
enter Priority, and I get nothing in the report.

What have I done wrong?

Here is the code behind the command button for the report
preview:

Private Sub ProjectListReportPreview_Click()
Dim lst As ListBox
Dim varItem As Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[PriorityList]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData
(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1)
'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut
& ")) AND "
End Select

'DATES
'If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then
'End, no start.
'strWhere = strWhere & "([Completion Date] <= "
& _
'Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
'End If
'Else
' If IsNull(Me.txtEndDate) Then
'Start, no end.
'strWhere = strWhere & "([Completion Date] >= "
& _
'Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' Else
'Both start and end.
' strWhere = strWhere & "([Completion Date]
Between " & _
' Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
' Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' End If
'End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Project Listing", acViewPreview, ,
strWhere

Set lst = Nothing
End Sub

-----Original Message-----
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date and ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from the non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case, it adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData (varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1) 'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut & ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date]
= " & _
Format
Click to expand...
(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & " And " & _
Format
Click to expand...
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub


Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...
Click to expand...
Click to expand...
 
Aha, light dawns...I was getting confused about what
variables represented what...now I understand...the field
in my table is actually called ProjPriority...once I put
that in, it works like a charm...Thanks so much for the
help.

-----Original Message-----
Presumably the report is based on a table or query that has a field named
"Priority"? If not, you won't be able to select the records where the
Priority field has one of the values in the list box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the help.
I tried to do what you said, and I started off small (ie
just trying to get the listbox for priority to work
first). I created the form with the priority listbox and
command button for the report.

When I click on the command button, I get prompted to
enter Priority, and I get nothing in the report.

What have I done wrong?

Here is the code behind the command button for the report
preview:

Private Sub ProjectListReportPreview_Click()
Dim lst As ListBox
Dim varItem As Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[PriorityList]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData
(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1)
'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut
& ")) AND "
End Select

'DATES
'If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then
'End, no start.
'strWhere = strWhere & "([Completion Date] <= "
& _
'Format (Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
'End If
'Else
' If IsNull(Me.txtEndDate) Then
'Start, no end.
'strWhere = strWhere & "([Completion Date] = "
& _
'Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' Else
'Both start and end.
' strWhere = strWhere & "([Completion Date]
Between " & _
' Format (Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
' Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' End If
'End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Project Listing", acViewPreview, ,
strWhere

Set lst = Nothing
End Sub

-----Original Message-----
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date
Click to expand...
and
ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from
Click to expand...
the
non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case,
Click to expand...
it
adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData (varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) -
Click to expand...
1) 'Remove
trailing comma.
strWhere = strWhere & "([Priority] IN (" &
Click to expand...
strOut
& ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format
Click to expand...
(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date]
= " & _
Format
Click to expand...
(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format
Click to expand...
(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
Format
Click to expand...
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub


Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...
Click to expand...


.
Click to expand...
 
Yikes...major problems now...

This filter form seems to work the first time I use it,
then the 2nd time I use it, I get an error that says:
"The expression On Click that you entered as the event
property setting produced the following error: Error
accessing file. Network Connection may have been lost."

And now none of the reports or modules run at all.

I was able to restore to a backup copy, but I'm wondering
what is causing this. When I use the report filter form
on a test database on my local hard drive I have no
problems. Its only when I import it into my live network
version that I get this error.

Any thoughts?

By the way I did fix the previous problem by referencing
the correct field name.

Thanks for your help.
-----Original Message-----
Presumably the report is based on a table or query that has a field named
"Priority"? If not, you won't be able to select the records where the
Priority field has one of the values in the list box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the help.
I tried to do what you said, and I started off small (ie
just trying to get the listbox for priority to work
first). I created the form with the priority listbox and
command button for the report.

When I click on the command button, I get prompted to
enter Priority, and I get nothing in the report.

What have I done wrong?

Here is the code behind the command button for the report
preview:

Private Sub ProjectListReportPreview_Click()
Dim lst As ListBox
Dim varItem As Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[PriorityList]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData
(varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) - 1)
'Remove trailing comma.
strWhere = strWhere & "([Priority] IN (" & strOut
& ")) AND "
End Select

'DATES
'If IsNull(Me.txtStartDate) Then
' If Not IsNull(Me.txtEndDate) Then
'End, no start.
'strWhere = strWhere & "([Completion Date] <= "
& _
'Format (Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
'End If
'Else
' If IsNull(Me.txtEndDate) Then
'Start, no end.
'strWhere = strWhere & "([Completion Date] = "
& _
'Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' Else
'Both start and end.
' strWhere = strWhere & "([Completion Date]
Between " & _
' Format (Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
' Format
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
' End If
'End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Project Listing", acViewPreview, ,
strWhere

Set lst = Nothing
End Sub

-----Original Message-----
Create an unbound form, with the controls for filtering the report:
- a list box, with its mutli-select property set to Simple so the user can
select more than one;
- a pair of text boxes for entering the starting date
Click to expand...
and
ending date;
- a combo based on a Value List, where the user can select Open, Closed, or
Both.
- a command button to open the report.

The example below shows how to build up a string from
Click to expand...
the
non-null text
boxes, to use as the WhereCondition for the OpenReport action. It picks up
the items from the multi-select listbox by looping through its ItemsSelected
collection, and then handles the dates. In each case,
Click to expand...
it
adds a trailing "
AND ", so you can build up as many conditions as you need. Then at the end
it chops off the trailing AND.

Private cmdPreview_Click()
Dim lst As Listbox
Dim varItem as Variant
Dim strOut As String
Dim strWhere As String
Dim lngLen As Long

'LISTBOX ITEMS
Set lst = Me.[NameOfYourListBoxHere]
Select Case lst.ItemsSelected.Count
Case 0
'Nothing selected.
Case 1
strWhere = strWhere & "([Priority] = """ &
lst.ItemData(lst.ItemsSelected(0)) & """) AND "
Case Else
'Multiple selection: loop through ItemsSelected.
For Each varItem In lst.ItemsSelected
If Not IsNull(varItem) Then
strOut = strOut & """" & lst.ItemData (varItem) & ""","
End If
Next
strOut = Left$(strOut, Len(strOut) -
Click to expand...
1) 'Remove
trailing comma.
strWhere = strWhere & "([Priority] IN (" &
Click to expand...
strOut
& ")) AND "
End Select

'DATES
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([Completion Date] <= " & _
Format
Click to expand...
(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([Completion Date]
= " & _
Format
Click to expand...
(Me.txtStartDate,"\#mm\/dd\/yyyy\#") & ") AND "
Else 'Both start and end.
strWhere = strWhere & "([Completion Date] Between " & _
Format
Click to expand...
(Me.txtEndDate, "\#mm\/dd\/yyyy\#")
& " And " & _
Format
Click to expand...
(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If
End If

'and so on for other controls

lngLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Set lst = Nothing
End Sub


Whats the easiest way to allow report filtering?

For example I have a table which has the following fields:
Priority (input values are A,B,C,D,E)
Leader (names)
Completion Date
Status (Open, Closed) [actually this field is calculated
by a query which looks at the completion date and compares
it to the current date]

I'd like my users to be able to enter the priority (A
through E, or all, or a combination like A+B), Completion
date range, and open or closed. And then have the report
only generate this data.

How do I do this?
Click to expand...
Click to expand...


.
Click to expand...
 
THANKS...that was it...I followed the instructions in the
link you gave and it works fine now...I REALLY APPRECIATE
YOUR HELP...THERE IS NO WAY I WOULD HAVE EVER FIGURED THAT
OUT NO MY OWN!!!!!!!!!!!!!!
 
Back
Top