list box and parameters

[rcollins]

I need my List box to carry over selected items to a report. Here is what I have

([Forms]![CommentCriteria]![List2])

but this doesnt work. In fact with this, I get an empty report. Need help please

[NeoPa]

There isn't really much of a question here to work with. It would help a lot if you expressed more clearly what you're attempting and what you hope to achieve.

I will take a stab that you have a Multi-Select ListBox control on a form and you want only those records associated with the selected items to be included in some report or other.

With so little info to work with I can only suggest a solution in very general terms of course :
Use VBA to build up a filter (SQL WHERE clause without the word WHERE) of only the selected items. Use the .ItemsSelected property of the ListBox control to identify the required items.

When you have prepared the filter string pass it as the WhereCondition parameter to the OpenReport() function you use to open the report.

[rcollins]

ok, I think i am a little confused. I thought I could put something in the query when the report opened that would look at what I selected from the list box. This is how I do my combobox. I have List0 for the list of items (which is already set as multple select) and the frield I am using in the report OPComments is SurveyCode. So my understanding from you is I would put it before:

    stDocName = "OPComments"
    DoCmd.OpenReport stDocName, acPreview
 

Here is what I am trying and It doesnt work. Do I have some syntax wrong?

select OPComments.SurveyCode where OPComments.SurveyCode = List0.ItemsSelected

[NeoPa]

@rcollins
Let me start by saying that ignoring my two comments about :

1. Using the CODE tags.
2. Posting in terms that make sense.

is not a great way to proceed.

As a long time member I'm trying not to slap an infraction onto your account, but you make this harder than it should be. When responding to a thread I prefer not to have to read up the whole thread again to work out what you're talking about.

What am I supposed to make of that (quoted)? Which query? Which report?

I will look at getting some answers for you, but please be more careful in future as this is frankly irritating (and totally unnecessary).

[NeoPa]

Sometimes, a bit of code can paint a thousand words. From this at least I can follow where you need a little more direction.

DoCmd.OpenReport has a parameter that you haven't used, called WhereCondition (not to be confused with FilterName). This is the fourth parameter. Parameters can also be passed by name if preferred :

Call DoCmd.OpenReport(ReportName:=stDocName, _
                      View:=acPreview, _
                      WhereCondition:=strWhere)

Before the call is made you need to translate the various items in your ListBox, using ItemsSelected (in a loop) to produce a string formatted like a SQL WHERE clause but without the "WHERE " part.

Dim strWhere As String
Dim varX As Variant
 
With Me.List0
    For Each varX In .ItemsSelected
        strWhere = strWhere & "," & .ItemData(varX)
    Next varX
    strWhere = "[SurveyCode] In(" & Mid(strWhere,2) & ")"
    Call DoCmd.OpenReport(ReportName:=stDocName, _
                          View:=acPreview, _
                          WhereCondition:=strWhere)
End With

From your posted SQL, your problem is that ItemsSelected is not a string value so would be entirely unusable in SQL directly. My earlier code illustrates how a SQL string should be set up as it works on very similar lines. Also, as the SQL that the report is built on is encapsulated within the report object itself, any change to the SQL would have to be entered as the property .RecordSource of the report.

[NeoPa]

As a last comment, that code assumes the [SurveyCode] field is numeric (I had to guess as this fundamental information was not available). If it turns out that this is a textual field instead, then simply replace line #6 in the posted code with :

strWhere = strWhere & ",'" & .ItemData(varX) & "'"

Notice the only difference here is the single-quotes around the values. This tells the SQL engine to treat the contents as a textual value rather than a numeric or date/time one.

[NeoPa]

A new (related) question was posted in here which has been split off as ListBox Problem.