Run report based on multi select list box Acc 2003

[dawnd3]

I am trying to figure out how to run a report based on multi selection list box. This is what I have so far. The table is a list of companies with a status field that has one of four possible status choices selected. I created a form with a list box and set the list box to multi select and put the four choices in as selections. (I didn't bind it to the table at all.) I then went into the underlying query of the report and put Forms!frmRunReports!List8 as the criteria for the status field. It opens with no records but doesn't throw up an error. What am I doing wrong?

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[Remou]

You will need to build an SQL string and either use it to create or modify a query or as the RecordSource of the report. For example, if the items are text:

For Each itm In Me.List2.ItemsSelected
    strtype = strtype & """,""" & Me.List2.Column(0, itm)
Next

strSQL="SELECT * From tblTable WHERE FieldX IN ("
strSQL = strSQL &  Mid(strtype, 3) & """)"

'Check if a query called qryQuery exists
'If it does not exist, create it.
'[red]If it does exist, permanently change it[/red]
   If DLookup("Name", "MSysObjects", "Name= 'qryQuery'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryQuery")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
    End If
'Open the query
DoCmd.OpenQuery "qryQuery"