Filtering a report by a specific criteria

[MelissaA]

Hello - I’m new to Access and I’m hoping that someone can help me! I need to
create a report that displays all of the issues that we have had with a
particular product number in our "tech issues" db…Could someone please let me
know how they think that I should approach this? Is there a way to have the
report pop-up a box where the end-user can select the product # from a list?
(I have a separate table containing the prod. # values, but I don’t know how
to make the query read it)

As it stands, I've created a separate "ProductFilter" query with the fields
that I need & I entered in the following criteria: Between [Starting Product
#] And [Ending Product #] but this doesn't seem to be very helpful or exactly
what we need...we don't need to have a range display in the report & we have
thousands of products in the list, so a selection list would be helpful...

Any help that anyone could provide would be much, MUCH appreciated!! Thank
you!

 

[Klatuu]

If you want to select one or more products to include in your report, the
best approach would be to use a form with a multi select list box to select
the products to include. Once the selection is complete, use a command
button to open the report and use the Where argument of the OpenReport method
to filter the data for the report.

Here is a function that I use in a similar situation. It evaluates the
selections in the list box and creates the criteria portion of the Where
argument.
********************
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
**********************************
Put the above code at the very top of your form module just below the Option
statments. Then to use it, put code something like this in the Click event
of the command button to run the form:

Dim strWhere As String

strWhere = BuildWhere("MyListBoxName")
If Len(strWhere) > 0 Then
strWhere = "[ProductID] = '" & strWhere & "'"
End If

Docmd.OpenReport "MyReportName", , ,strWhere

Note that if the user has selected no products from the list box, the report
will include all products.