opening report with filter

[Fipp]

I am trying to open the report "hitchartrpt" With it being filtered on
"opponent", "season" and "team"

I have a filter form and on that form there are 3 list boxes. season is
single select, both opponent and team are multi select list boxes. For both
multi select list boxes I have written some code that enters the values of
the multi select list boxes into data as follows (ex: "Hawaii","Texas") this
data is entered into the control [opponentselected] and [teamselected] for
the respective list boxes.

Here is the code that I have written on the click of the "hitchartrpt"
button, I am not sure what I am doing wrong?

Private Sub hitchartrpt_Click()
DoCmd.OpenReport "hitchartrpt", acViewPreview, , _
"opponent in (" & Me.opponentselected & ") AND " & _
"season = " & Me.cboseason & " AND " & _
"team in (" & Me.teamselect & ")"
End Sub

 

[Scott McDaniel]

I am trying to open the report "hitchartrpt" With it being filtered on
"opponent", "season" and "team"

I have a filter form and on that form there are 3 list boxes. season is
single select, both opponent and team are multi select list boxes. For both
multi select list boxes I have written some code that enters the values of
the multi select list boxes into data as follows (ex: "Hawaii","Texas") this
data is entered into the control [opponentselected] and [teamselected] for
the respective list boxes.

Here is the code that I have written on the click of the "hitchartrpt"
button, I am not sure what I am doing wrong?

Private Sub hitchartrpt_Click()
DoCmd.OpenReport "hitchartrpt", acViewPreview, , _
"opponent in (" & Me.opponentselected & ") AND " & _
"season = " & Me.cboseason & " AND " & _
"team in (" & Me.teamselect & ")"
End Sub

Are opponentSelect and TeamSelect your listboxes? If so, you'll need to iterate through the ItemsSelected property to
grab the values chosen:

Dim sData As String
Dim var as Variant

For each var in Me.opponentselected.ItemsSelected
'this will get the value in the FIRST column of the listbox
sData = sData & me.opponentselected.Column(0,var) & ","
Next var
'trim the trailing ,
sdata = Left(sData, Len(sData)-1)

Now use sData in your IN statement:
"opponent in (" & sData & ") AND " & _

Do the same thing with the next listbox.

Also: Is "season" a Text value or Numeric? If it's Text, you'll need to surround it with single quotes:

"season='" & Me.cboSeason & "' AND " &_


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Fipp]

Season is numeric, opponentselect and teamselect are text boxes with the
values entered by code in the following format (ex: "Hawaii","Texas")

 

[Scott McDaniel]

Season is numeric, opponentselect and teamselect are text boxes with the
values entered by code in the following format (ex: "Hawaii","Texas")

Are they entered EXACTLY in that manner, with the quotes between them? If so, you'll have to remove them. Your string
should look like

IN (Hawaii, Texas)

You can remove the quotes with the Replace function:

Dim sOpponent As String

sOpponent = replace(Me.opponentSelect, """","")

Now use sOpponent in your Where clause instead of Me.opponentselect ... same would apply for teamselect ..

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[John W. Vinson]

Are they entered EXACTLY in that manner, with the quotes between them? If so, you'll have to remove them. Your string
should look like

IN (Hawaii, Texas)

Ummm...?

Not in my experience. String criteria - whether in an = or an IN() clause -
must be delimited by either ' or ".


John W. Vinson [MVP]