RE: List Box to Filter Report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

JaRa,

The code is now multiselecting, but I still get an error of "missing
operator" for each item selected.
I think some commas need to be inserted into the strWhere as they are text
values, but I still haven't got to grips with this yet.

Again, thanks for your time on this.

Winger



"JaRa" wrote:

> Yes Indeed sorry
> --
> - Raoul Jacobs
>
> The nature of developping is sharing knowledge.
>
>
> "Winger" wrote:
>
> > JaRa,
> >
> > I'm also assuming that in your code "iif(strSQL" should read "iif(strWhere"
> >
> > Winger
> >
> > "JaRa" wrote:
> >
> > > Sorry small correction
> > >
> > >
> > > Dim frm As Form, ctl As Control
> > > Dim varItem As Variant
> > > Dim strWhere As String
> > > Set frm = Forms!frmMatchRestoFund
> > > Set ctl = frm!PrintList
> > >
> > > strWhere = ""
> > >
> > > For Each varItem In ctl.ItemsSelected
> > > strWhere = strWhere & iif(strSQL="", "[FunderProgramme]=" &
> > > ctl.ItemData(varItem),
> > > " OR [FunderProgramme]=" & ctl.ItemData(varItem))
> > > Next varItem
> > >
> > >
> > > DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere
> > >
> > > --
> > > - Raoul Jacobs
> > >
> > > The nature of developping is sharing knowledge.
> > >
> > >
> > > "Winger" wrote:
> > >
> > > > I'm trying to create a string of values from a list box and pass this to the
> > > > WHERE condition to open a report.
> > > >
> > > > I've tried code given in previous posted answers, but when adapting it to my
> > > > own DB, I'm going wrong somewhere.
> > > >
> > > > Dim frm As Form, ctl As Control
> > > > Dim varItem As Variant
> > > > Dim strSQL As String
> > > > Set frm = Forms!frmMatchRestoFund
> > > > Set ctl = frm!PrintList
> > > >
> > > > strSQL = "[FunderProgramme]="
> > > >
> > > > For Each varItem In ctl.ItemsSelected
> > > > strSQL = strSQL & ctl.ItemData(varItem) & " OR [FunderProgramme]="
> > > > Next varItem
> > > >
> > > > strSQL = Left$(strSQL, Len(strSQL) - 22)
> > > >
> > > > DoCmd.OpenReport "rptFunder1", acViewPreview, , "([strSQL])"
> > > >
> > > >
> > > > The problems:
> > > > 1) The example I took this from had to truncate 12 characters off the string
> > > > as it was " OR [EmpID]=". If I change this to 22 because my code adds " OR
> > > > [FunderProgramme]=" I get an error message "invalid call or procedure".
> > > > 2) If I leave the truncate as "strSQL = Left$(strSQL, Len(strSQL) - 12), I
> > > > get a message box asking me for the value of "strSQL".
> > > >
> > > > Any pointers gratefully received.
> > > >
> > > > thanks
> > > >
> > > > Winger
> > > >
.

Relevant Pages

  • RE: List Box to Filter Report
    ... "Winger" wrote: ... >> Dim frm As Form, ... >> Dim varItem As Variant ... >> The nature of developping is sharing knowledge. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Send Message on Behalf Question
    ... Dim iConf ... Dim emName As String, varItem As Variant ... For Each varItem In Me.lboRequestor.ItemsSelected ... 'add the requestor to the e-mail list recipients ...
    (microsoft.public.outlook.program_vba)
  • RE: Use Multi-Select List boxes as query parameters
    ... Dim frm As Form, ctl As Control ... Dim varItem As Variant ...
    (microsoft.public.access.forms)
  • RE: Use Multi-Select List boxes as query parameters
    ... I put the list box is the form header because if you decide to cahnge the ... Dim frm As Form, ctl As Control ... Dim varItem As Variant ...
    (microsoft.public.access.forms)
  • RE: Seeking Help With VBA and SQL Instant Query
    ... names of the fields you want to see in the query. ... Dim db As DAO.Database ... Dim strBroker As String ... For Each varItem In Me.lstBroker.ItemsSelected ...
    (microsoft.public.access.modulesdaovba)