trying to get Filter on Subform to work with Multiselect in Listbo [WiredBox.Net

> Gator,
>
> With multi-select set to something other than "None", you will have to loop
> through the ItemsSelected collection of the listbox, and concatenate the
> values. In your case, it looks like the bound field of your list is text, so
> you will have to wrap the values in quotes. I have a function (not currently
> available, so the following is air code) that I use in these instances. The
> function looks something like:
>
> Public Function fnMultiList(lst as listbox, _
> Optional WrapWith as String = "'") as
> variant
>
> Dim varItem as Variant
>
> fnMultiList = NULL
> if lst.ItemsSelected.count = 0 then Exit function
>
> for each varItem in lst.itemsselected
>
> fnMultiList = (fnMultiList + ",") _
> & WrapWith _
> & lst.column(lst.boundcolumn-1, varItem) _
> & WrapWith
> Next
>
> if instr(fnMultiList, ",") = 0 then
> fnMultiList = "= " & fnMultiList
> Else
> fnMultiList = " IN (" & fnMultiList & ")"
> endif
>
> End Function
>
> This code will return a NULL value if no items are selected (I usually set
> this up so that the query doesn't include that field in the SQL if no items
> are selected.
>
> Otherwise, it will return an equal sign followed by a single value (wrapped
> by whatever you used as your "WrapWith" value. If the bound column in your
> list is numeric, then pass the function an empty string ("").
>
> If there is more than one item selected, it will return an IN ( ) clause,
> that looks something like: IN ('value1', 'value2')
>
> Assuming that list 11 is the one you want to change into a multi-select, the
> way you would use this in your code would be something like:
>
> strSQL = "SELECT Format([DateDep], 'yyyymm'), " _
> & "Format(Sum(Amount), 'Currency'), " _
> & "Account " _
> & "FROM Deposits " _
> & "WHERE Account = '" & me.list13 & "'" _
> & (" AND [Fund] " + fnMultiList(me.list11)) _
> & " GROUP BY Format([DateDep], 'yyyymm'), _
> & "[Account] " _
> & "ORDER BY Format([DateDep], 'yyyymm') Desc"
> me.list15.RowSource = strsql
>
> By wrapping the line after the WHERE in ( ) and using the + to concatenate
> the items you ensure that if no items are selected in list11, then the [Fund]
> field will not be included in the query.
>
> Since this is untested (air code), you might want to test it before you
> implement it.
>
> --
> HTH
> Dale
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>
> "Gator" wrote:
>
> > Most of the code here is just FYI....my question really gets to the
> > TabCtl0_Change() event. The Filter works well with the Multiselect set as
> > None. How would I adjust the code to make the Filter work with Multiselect
> > set at Simple?
> >
> > thanks
> >
> > Private Sub Frame17_Click()
> > If Frame17.Value = 1 Then
> > List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
> > Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> > List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
> > Account ORDER BY Format((DateDep),'yyyymm') DESC;"
> > Else
> > List15.RowSource = "SELECT Format((DateDep),'yyyy'),
> > Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> > List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
> > Account ORDER BY Format((DateDep),'yyyy') DESC;"
> >
> > End If
> > End Sub
> >
> > Private Sub List11_Click()
> > List13.RowSource = "SELECT Account, AccountName, Fund FROM Deposits WHERE
> > (Fund= '" & List11 & "') GROUP BY Account, AccountName, Fund;"
> >
> > End Sub
> > Private Sub List13_Click()
> > If Frame17.Value = 1 Then
> > List15.RowSource = "SELECT Format((DateDep),'yyyymm'),
> > Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> > List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyymm'),
> > Account ORDER BY Format((DateDep),'yyyymm') DESC;"
> > Else
> > List15.RowSource = "SELECT Format((DateDep),'yyyy'),
> > Format(SUM(Amount),'currency'), Account FROM Deposits WHERE (Account='" &
> > List13 & "') AND (Fund='" & List11 & "') GROUP BY Format((DateDep),'yyyy'),
> > Account ORDER BY Format((DateDep),'yyyy') DESC;"
> >
> > End If
> > End Sub
> >
> >
> > Private Sub TabCtl0_Change()
> > Dim strFilter As String
> > With Me.TabCtl0
> > strFilter = "(Deposits.Account='" & List13 & "') AND (Deposits.Fund='" &
> > List11 & "') AND ((Year(Deposits.DateDep))='" & (Left(List15.Column(0), 4)) &
> > "') AND ((Month(Deposits.DateDep))='" & (Right(List15.Column(0), 2)) & "')"
> > End With
> >
> > With Me.Deposits_subform.Form
> > .Filter = strFilter
> > .FilterOn = True
> > .OrderBy = "DateDep DESC"
> > .OrderByOn = True
> > End With
> > End Sub