Re: MultiSelect Property

Thanks very much for the detailed reply - all is working as required now.
Mark

"Douglas J. Steele" wrote:

> Actually, there's a flaw in your loop.
>
> stOR = " OR ((([Service Items Table].[Contract Name])='": stCriteria =
> "'))"
> For Each vItm In Me!lbSelectContract.ItemsSelected
> stWhat = stOR & Me!lbSelectContract.ItemData(vItm)
> stWhat = stWhat & stCriteria
> Next vItm
>
> replaces stWhat each time, as opposed to building on what's already there.
>
> Try the following function instead of what you've got:
>
> Private Sub CntrlRunIssuesReport_Click()
>
> Dim vItm As Variant
> Dim stWhat As String
> Dim stCriteria As String
> Dim stSQL As String
> Dim loqd As QueryDef
>
> For Each vItm In Me!lbSelectContract.ItemsSelected
> stWhat = stWhat & Chr$(39) & _
> Me!lbSelectContract.ItemData(vItm) & _
> Chr$(39) & ", "
> Next vItm
>
> stSQL = "SELECT * FROM [Service Items Table] "
>
> ' Check whether anything was selected.
> ' If so, remove the last ", " from the end
>
> If Len(stWhat) > 0 Then
> stWhat = Left$(stWhat, Len(stWhat) - 2)
> stSQL = stSQL & "WHERE [Contract Name] IN (" & _
> stWhat & ")"
> End If
>
> Set loqd = CurrentDb.QueryDefs("qryMultiSelTest")
> loqd.SQL = stSQL
> loqd.Close
> DoCmd.OpenQuery "qryMultiSelTest"
>
> End Sub
>
> It should solve your first two questions.
>
> One way to use a parameter is to have the string built up as ,Oracle,Medusa,
> (in other words, don't bother with the quotes around the individual items,
> and make sure there's a comma at the beginning and end.
>
> WHERE InStr("," & [Service Items Table].[Contract Name] & ",", [Parameter])
> > 0
>
> What this would do is look for the contract name (with commas on either
> side) in the entire parameter. If it finds it, the result of the InStr
> function will be the position in the string, so will be greater than 0. If
> it doesn't find it, InStr returns 0.
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Mark A" <MarkA@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:9A39735F-86FD-42E9-A0E3-72373B1EA024@xxxxxxxxxxxxxxxx
> > Sorry, yes - I was rather vague. There are three problems:
> > 1) I cannot figure out how to build the sql string without having the
> > "Dummy" entry in the first part of the SQL string (however there are no
> > records with a value of "Dummy" and so it isn't critical that it is in
> > the
> > SQL query). (see code below)
> > 2) In the example below I used the MultiSelect=simple to choose values of
> > "Oracle" and "Medusa" however only the value I selected first ("Oracle")
> > in
> > the multiselect list box appears in the SQL query. I thought the "For Each
> > vItm" loop would build the SQL query with every multiselect item. (see
> > code
> > below)
> > 3) How do I pass the multiselect values as parameters to a report rather
> > than just a standalone query. Currently the code just uses
> > "DoCmd.OpenQuery
> > "qryMultiSelTest". I know I can use:
> > " stDocName = "Contracts Report"
> > DoCmd.OpenReport stDocName, acPreview"
> >
> > ...to open a report but how do I pass the parameters to the reports
> > underlyin query at the same time?
> >
> > THis is the SQL code generated when running qryMultiSelTest:
> > SELECT *
> > FROM [Service Items Table]
> > WHERE ((([Service Items Table].[Contract Name])='Dummy' Or ([Service Items
> > Table].[Contract Name])='Oracle'));
> >
> > Thanks again
> > Mark
> >
> > "Douglas J. Steele" wrote:
> >
> >> What specifically are the problems? "isn't looping correctly" and "only
> >> builds ... with the first multiselect property" aren't really that
> >> descriptive.
> >>
> >> After your code has run, what does the SQL in qryMultiSelTest look like?
> >> (Open the query in design mode, select SQL View from the View menu, copy
> >> the
> >> SQL to the clipboard and paste it here)
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >>
> >> "Mark A" <MarkA@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:49E8E4C5-FD0F-410B-AB3E-F9F378AAFA19@xxxxxxxxxxxxxxxx
> >> > Hi.
> >> > I found the examples from your links very helpful and they nearly
> >> > achieve
> >> > what I need: to launch a report, the underlying query for which is
> >> > built
> >> > up
> >> > using multiselect parameters. I'm also finding that the code isn't
> >> > looping
> >> > correctly and only builds my SQL string with the first ultiselect
> >> > property.
> >> > My code is below - could you give some pointers please?
> >> > Thanks, Mark
> >> >
> >> > Private Sub CntrlRunIssuesReport_Click()
> >> >
> >> > Dim vItm As Variant
> >> > Dim stWhat As String
> >> > Dim stOR As String
> >> > Dim stCriteria As String
> >> > Dim stSQL As String
> >> > Dim loqd As QueryDef
> >> >
> >> > stOR = " OR ((([Service Items Table].[Contract Name])='":
> >> > stCriteria
> >> > = "'))"
> >> > For Each vItm In Me!lbSelectContract.ItemsSelected
> >> > stWhat = stOR & Me!lbSelectContract.ItemData(vItm)
> >> > stWhat = stWhat & stCriteria
> >> > Next vItm
> >> > Me!TxtCriteria = stWhat
> >> > Set loqd = CurrentDb.QueryDefs("qryMultiSelTest")
> >> > stSQL = "SELECT * FROM [Service Items Table] WHERE ((([Service Items
> >> > Table].[Contract Name])='Test'))"
> >> > stSQL = stSQL & Me!TxtCriteria
> >> > loqd.SQL = stSQL
> >> > loqd.Close
> >> > DoCmd.OpenQuery "qryMultiSelTest"
> >> > End Sub
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> I doubt you'll find any white papers about how to do that, as it's
> >> >> definitely not recommended to store multiple values in a single field.
> >> >>
> >> >> Take a look at http://www.mvps.org/access/reports/rpt0005.htm or
> >> >> http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
> >> >> how
> >> >> to
> >> >> retrieve the selected values.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >>
> >> >> "Lawguy" <Lawguy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:6B19A456-C25A-4C29-80A6-CCE7E491CE3D@xxxxxxxxxxxxxxxx
> >> >> >I have a listbox of names from which I would like to (a) select
> >> >> >multiple
> >> >> > names and then (b) use a command button click event to copy the
> >> >> > selections
> >> >> > to
> >> >> > a table field. The destination field will not have need of
> >> >> > normalization,
> >> >> > sorting, etc., only a general search. I want to have the listbox
> >> >> > available
> >> >> > for three fields: Author(s), Recipient(s), and Copied to. This is
> >> >> > similarin
> >> >> > appearance to what a wizard look like when building a form, the
> >> >> > different
> >> >> > being that once the selected items are selected and the click event
> >> >> > initiated, there is not further need of manipulation of the field to
> >> >> > where
> >> >> > copied. Any white papers, work arounds, or suggestions would be
> >> >> > appreciated.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.