How make query criteria come from selected records on a listbox

[SteveL]

I have a form which contains a listbox. It is a multi-select listbox. And I have a button on the form which runs a report. But I want the report to only show the selected records from the listbox. The report gets its records from an underlying query. But how do I make the selected records on the listbox become the query criteria?

 

[pbaldy]

One way:

  strSQL = "Type = '"

  For Each varItem In ctl.ItemsSelected
      strSQL = strSQL & ctl.ItemData(varItem) & "' OR Type = '"
  Next varItem

  strSQL = left$(strSQL, Len(strSQL) - 12)

  DoCmd.OpenReport "rptDriversByType", acViewPreview, , strSQL

 

[SteveL]

Didn't work... I get an error that says "Enter parameter value : Type". Here's my code...

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

strSQL = "Type = '"

For Each varItem In List34.ItemsSelected
strSQL = strSQL & List34.ItemData(varItem) & "' OR Type = '"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 12)

DoCmd.OpenReport "rptScrapReportRankByDollars", acViewPreview, , strSQL


Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

 

[pbaldy]

Sorry, that was just the "guts" of it. Here's the whole thing:

  Dim ctl As Control
  Dim varItem As Variant
  Dim strSQL As String

  'build wherecondition based on selections

  Set ctl = Me.lstTypes

  strSQL = "Type = '"

  For Each varItem In ctl.ItemsSelected
      strSQL = strSQL & ctl.ItemData(varItem) & "' OR Type = '"
  Next varItem

  'Trim the end of strSQL
  strSQL = left$(strSQL, Len(strSQL) - 12)

  DoCmd.OpenReport "rptDriversByType", acViewPreview, , strSQL

You also need to change "Type", as that's the field in the data that this particular report is to be filtered on (and for the record, Type is not a good name for a field, as it's a reserved word; this is from a sample db that I didn't create).

 

[pbaldy]

Oh, and if your value is numeric rather than text, you won't want the single quotes around the value. You may also need to change the number in the second to the last line; its purpose is to trim off the leftover bit at the end.