query based off Multi select list box

[Fipp]

I have a form titled "reportfilterfrm"
on this form I have 3 fields that I want my query to be based off of
[season] [team] [opponent]

[opponent] is a multi select list box. I wrote the following code and have a
textbox called [opponentselected] the code fills this box in with the names
of the opponents selected in the list box with a comma. Ex.
"opponentA,OpponentB"

Private Sub opponentscout_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!cboopponent
If .MultiSelect = 0 Then
Me!opponentselected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.opponentselected = strList
End If
End Sub

Now I have the following sql for my query that binds it to the [season]
field and the [team] field on my form.

SELECT maingameformation.*, mainplay.*, maingameformation.season,
maingameformation.team, maingameformation.opponent
FROM maingameformation INNER JOIN mainplay ON maingameformation.playid =
mainplay.playid
WHERE (((maingameformation.season)=[forms]![reportfilterfrm]![cboseason])
AND ((maingameformation.team)=[forms]![reportfilterfrm]![cboteam]));


All of this works. I am now trying to bind the [opponentselected] field to
the query. I can't seem to make this work?
Any Suggestions?

 

[Bob Quintal]

I have a form titled "reportfilterfrm"
on this form I have 3 fields that I want my query to be based
off of [season] [team] [opponent]

[opponent] is a multi select list box. I wrote the following
code and have a textbox called [opponentselected] the code
fills this box in with the names of the opponents selected in
the list box with a comma. Ex. "opponentA,OpponentB"

Private Sub opponentscout_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!cboopponent
If .MultiSelect = 0 Then
Me!opponentselected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.opponentselected = strList
End If
End Sub

Now I have the following sql for my query that binds it to the
[season] field and the [team] field on my form.

SELECT maingameformation.*, mainplay.*,
maingameformation.season, maingameformation.team,
maingameformation.opponent FROM maingameformation INNER JOIN
mainplay ON maingameformation.playid = mainplay.playid
WHERE
(((maingameformation.season)=[forms]![reportfilterfrm]! [cboseas
on]) AND
((maingameformation.team)=[forms]![reportfilterfrm]! [cboteam]))
;

AND ( maingame.opponent IN([forms]![reportfilterfrm]!
opponentSelected))

you may need to modify your code to envelop each opponent in
quotes.
e.g. strList = strList & """" & .Column(0, varItem) & ""","

 

[Fipp]

opponent and opponentselect are text boxes and I have noticed that the value
I get from my code doesn't have quotes around the results? I am not sure if
that matters.
Ex. "teamA","teamB"

 

[Fipp]

That works now the results of the [opponentselected] field are as follows.
TeamA","TeamB"

I tried to bind that to the query as follows and it didn't work?

In (" & [forms]![reportfilterfrm]![opponentselected] & ")

I also tried the following:
In ([forms]![reportfilterfrm]![opponentselected])

Neither of these worked?
Any Suggestions?

 

[Bob Quintal]

That works now the results of the [opponentselected] field are
as follows. TeamA","TeamB"

I tried to bind that to the query as follows and it didn't
work?

In (" & [forms]![reportfilterfrm]![opponentselected] & ")

I also tried the following:
In ([forms]![reportfilterfrm]![opponentselected])

Neither of these worked?
Any Suggestions?

Can you leave the criteria out of the query and pass them as a
where clause to the form or report that uses the query.?

docmd.openform "MyForm1",,,"oponent in (" & Me.opponentselected
& ")"

does work.