Hi

Having some problems running a report with a list box selection. Below is
the code I am using.
The code should create a criteria (in the field Seperator) to run a report
based upon a query, however this is not happening. If I enter the criteria
directly into the query and then run the query it works fine, so it would
appear the criteria is not being passed to the query.
The problem is the report runs and shows all records from the table
regardless of the selection in the list box

Private Sub cmdRunReport_Click()

Dim varItem As Variant
Dim strCriteria As String
Dim strAssistFMJobs As String
Dim stDocName As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobs



For Each varItem In ctrl.ItemsSelected
strAssistFMJobs = strAssistFMJobs & "," & ctrl.ItemData(varItem)
Next varItem

strAssistFMJobs = Mid(strAssistFMJobs, 2)
strCriteria = "Seperator In(" & strAssistFMJobs & ")"


If Len(strAssistFMJobs) = 0 Then
MsgBox "You did not select anything from the list", vbExclamation,
"Nothing to find!"
Exit Sub
End If

stDocName = "Data Sheet for Clients"
DoCmd.OpenReport stDocName, acViewPreview, strCriteria

End Sub

Thanks

Richard

richard wrote:
> Having some problems running a report with a list box selection.
> Below is the code I am using.
> The code should create a criteria (in the field Seperator) to run a
> report based upon a query, however this is not happening. If I enter
> the criteria directly into the query and then run the query it works
> fine, so it would appear the criteria is not being passed to the
> query.
[snip]

> stDocName = "Data Sheet for Clients"
> DoCmd.OpenReport stDocName, acViewPreview, strCriteria

You are putting your criteria in the Filter argument. It belongs in the Where
argument. Just add one more comma...

DoCmd.OpenReport stDocName, acViewPreview,, strCriteria

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Seperator is spelled incorrectly. The correct spelling is Separator.
"richard" <(E-Mail Removed)> wrote in message
news:1B312683-C375-48D8-8600-(E-Mail Removed)...
> Hi
>
> Having some problems running a report with a list box selection. Below is
> the code I am using.
> The code should create a criteria (in the field Seperator) to run a report
> based upon a query, however this is not happening. If I enter the criteria
> directly into the query and then run the query it works fine, so it would
> appear the criteria is not being passed to the query.
> The problem is the report runs and shows all records from the table
> regardless of the selection in the list box
>
> Private Sub cmdRunReport_Click()
>
> Dim varItem As Variant
> Dim strCriteria As String
> Dim strAssistFMJobs As String
> Dim stDocName As String
> Dim ctrl As Control
>
> Set ctrl = Me.lstAssistFMJobs
>
>
>
> For Each varItem In ctrl.ItemsSelected
> strAssistFMJobs = strAssistFMJobs & "," & ctrl.ItemData(varItem)
> Next varItem
>
> strAssistFMJobs = Mid(strAssistFMJobs, 2)
> strCriteria = "Seperator In(" & strAssistFMJobs & ")"
>
>
> If Len(strAssistFMJobs) = 0 Then
> MsgBox "You did not select anything from the list", vbExclamation,
> "Nothing to find!"
> Exit Sub
> End If
>
> stDocName = "Data Sheet for Clients"
> DoCmd.OpenReport stDocName, acViewPreview, strCriteria
>
> End Sub
>
> Thanks
>
> Richard