Discussion Groups Home

How to use a parameter query with multiple values? in Access Database Queries

10/27/2004 10:27 AM PST

Question

	Is it possible to have parameter query that uses multiple values? Example: a large database contains a field named "ClassCode". The user wants to specify any number of values for this field (like "JSB", "JSC", "JGS") or retrieve all records. The only parameter query that I get to work only returns 1 value. Is it possible to retrieve multiple values? If so, what is the syntax? Thank You!

Was this post helpful to you?

Top

Ken Snell [MVP]

10/27/2004 10:39 AM PST

Answer

No, a parameter will get one value. Use multiple parameters to get the
different things you want.

Or use a form that allows the user to enter the choices on the form, and
then have the query read the parameters from the form.

--

Ken Snell
<MS ACCESS MVP>

"Norm Lundquist" <NormLundquist@discussions.microsoft.com> wrote in message
news:1FEC49EB-5915-47EC-AC0B-AB634FF97C8F@microsoft.com...
> Is it possible to have parameter query that uses multiple values?
>
> Example: a large database contains a field named "ClassCode". The user
> wants to specify any number of values for this field (like "JSB", "JSC",
> "JGS") or retrieve all records.
>
> The only parameter query that I get to work only returns 1 value. Is it
> possible to retrieve multiple values? If so, what is the syntax?
>
> Thank You!

Was this post helpful to you?

Top

Tom Ellison

10/27/2004 12:13 PM PST

Dear Norm:

It is possible to do this using a parameter, although it isn't the
best technique.

If the user enters a list like JSB,JSC,JGS (without quotes in this
example, separated by commas and no spaces, then you can search that
string with the InStr function using the values in the subject column.
This can work as long as there are no commas within the subject
column's values.

To do this, append a comma before and after the parameter list and
before and after the value from the column:

WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0

This places a burden on the user to use commas (without spaces)
exactly, and to spell each item exactly. I have found this is often
too much to ask. Instead, a multi-select list box on a form is much
easier for the users, although it is a bit of work for you to
implement.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
<NormLundquist@discussions.microsoft.com> wrote:

>Is it possible to have parameter query that uses multiple values?
>
>Example: a large database contains a field named "ClassCode". The user
>wants to specify any number of values for this field (like "JSB", "JSC",
>"JGS") or retrieve all records.
>
>The only parameter query that I get to work only returns 1 value. Is it
>possible to retrieve multiple values? If so, what is the syntax?
>
>Thank You!

Was this post helpful to you?

Top

5/22/2008 12:45 PM PST

I'm working on this in Access 2007 and i know these posts happened a while
ago - but how would the parameter be set up from the multi-select list?

I'm not finding the results that i'm looking for.

Thanks for the help

AJ

"Tom Ellison" wrote:

> Dear Norm:
>
> It is possible to do this using a parameter, although it isn't the
> best technique.
>
> If the user enters a list like JSB,JSC,JGS (without quotes in this
> example, separated by commas and no spaces, then you can search that
> string with the InStr function using the values in the subject column.
> This can work as long as there are no commas within the subject
> column's values.
>
> To do this, append a comma before and after the parameter list and
> before and after the value from the column:
>
> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
>
> This places a burden on the user to use commas (without spaces)
> exactly, and to spell each item exactly. I have found this is often
> too much to ask. Instead, a multi-select list box on a form is much
> easier for the users, although it is a bit of work for you to
> implement.
>
> Tom Ellison
> Microsoft Access MVP
> Ellison Enterprises - Your One Stop IT Experts
>
>
> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> <NormLundquist@discussions.microsoft.com> wrote:
>
> >Is it possible to have parameter query that uses multiple values?
> >
> >Example: a large database contains a field named "ClassCode". The user
> >wants to specify any number of values for this field (like "JSB", "JSC",
> >"JGS") or retrieve all records.
> >
> >The only parameter query that I get to work only returns 1 value. Is it
> >possible to retrieve multiple values? If so, what is the syntax?
> >
> >Thank You!
>
>

Was this post helpful to you?

Top

Michel Walsh

5/22/2008 1:34 PM PST

You loop over the Selected items, concatenate the values to be compared (not
necessary the values displayed) as a delimited list and use that list as
'argument'. You can also use the operator LIKE instead of InStr so your
query can be used outside Access (such as from Excel):

WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn &
"*")

which assumes your delimited list of value has no unwanted spaces, ie, like
"1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.

Vanderghast, Access MVP

"AJ" <AJ@discussions.microsoft.com> wrote in message
news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> I'm working on this in Access 2007 and i know these posts happened a while
> ago - but how would the parameter be set up from the multi-select list?
>
> I'm not finding the results that i'm looking for.
>
> Thanks for the help
>
> AJ
>
> "Tom Ellison" wrote:
>
>> Dear Norm:
>>
>> It is possible to do this using a parameter, although it isn't the
>> best technique.
>>
>> If the user enters a list like JSB,JSC,JGS (without quotes in this
>> example, separated by commas and no spaces, then you can search that
>> string with the InStr function using the values in the subject column.
>> This can work as long as there are no commas within the subject
>> column's values.
>>
>> To do this, append a comma before and after the parameter list and
>> before and after the value from the column:
>>
>> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
>>
>> This places a burden on the user to use commas (without spaces)
>> exactly, and to spell each item exactly. I have found this is often
>> too much to ask. Instead, a multi-select list box on a form is much
>> easier for the users, although it is a bit of work for you to
>> implement.
>>
>> Tom Ellison
>> Microsoft Access MVP
>> Ellison Enterprises - Your One Stop IT Experts
>>
>>
>> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
>> <NormLundquist@discussions.microsoft.com> wrote:
>>
>> >Is it possible to have parameter query that uses multiple values?
>> >
>> >Example: a large database contains a field named "ClassCode". The user
>> >wants to specify any number of values for this field (like "JSB", "JSC",
>> >"JGS") or retrieve all records.
>> >
>> >The only parameter query that I get to work only returns 1 value. Is it
>> >possible to retrieve multiple values? If so, what is the syntax?
>> >
>> >Thank You!
>>
>>

Was this post helpful to you?

Top

5/22/2008 2:23 PM PST

Thanks for the prompt response Michel -
When I use the where expression that you gave I receive the error of
"Undefined function 'where' in expression"

When I used your LIKE.... expression then all results from the table show
rather than just the values selected in the multiple select list box.

To further explain what I've done - I have a listbox which is named
"Location" on a form currently saved as Form1

What I tried to do with your parameter in my query was this..

Where ("," & [forms]![form1]![location] & ",")

Thanks for the help!

"Michel Walsh" wrote:

> You loop over the Selected items, concatenate the values to be compared (not
> necessary the values displayed) as a delimited list and use that list as
> 'argument'. You can also use the operator LIKE instead of InStr so your
> query can be used outside Access (such as from Excel):
>
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn &
> "*")
>
>
> which assumes your delimited list of value has no unwanted spaces, ie, like
> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>
>
>
> Vanderghast, Access MVP
>
>
>
> "AJ" <AJ@discussions.microsoft.com> wrote in message
> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> > I'm working on this in Access 2007 and i know these posts happened a while
> > ago - but how would the parameter be set up from the multi-select list?
> >
> > I'm not finding the results that i'm looking for.
> >
> > Thanks for the help
> >
> > AJ
> >
> > "Tom Ellison" wrote:
> >
> >> Dear Norm:
> >>
> >> It is possible to do this using a parameter, although it isn't the
> >> best technique.
> >>
> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
> >> example, separated by commas and no spaces, then you can search that
> >> string with the InStr function using the values in the subject column.
> >> This can work as long as there are no commas within the subject
> >> column's values.
> >>
> >> To do this, append a comma before and after the parameter list and
> >> before and after the value from the column:
> >>
> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
> >>
> >> This places a burden on the user to use commas (without spaces)
> >> exactly, and to spell each item exactly. I have found this is often
> >> too much to ask. Instead, a multi-select list box on a form is much
> >> easier for the users, although it is a bit of work for you to
> >> implement.
> >>
> >> Tom Ellison
> >> Microsoft Access MVP
> >> Ellison Enterprises - Your One Stop IT Experts
> >>
> >>
> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> >> <NormLundquist@discussions.microsoft.com> wrote:
> >>
> >> >Is it possible to have parameter query that uses multiple values?
> >> >
> >> >Example: a large database contains a field named "ClassCode". The user
> >> >wants to specify any number of values for this field (like "JSB", "JSC",
> >> >"JGS") or retrieve all records.
> >> >
> >> >The only parameter query that I get to work only returns 1 value. Is it
> >> >possible to retrieve multiple values? If so, what is the syntax?
> >> >
> >> >Thank You!
> >>
> >>
>
>
>

Was this post helpful to you?

Top

Michel Walsh

5/23/2008 2:54 AM PST

You have to be in SQL view, to be easier.

Your query should look like:

SELECT ...
FROM ...
WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn &
"*")

Vanderghast, Access MVP

"AJ" <AJ@discussions.microsoft.com> wrote in message
news:D4BBCB31-C504-4D77-9EB5-CEB8ACE2A432@microsoft.com...
> Thanks for the prompt response Michel -
> When I use the where expression that you gave I receive the error of
> "Undefined function 'where' in expression"
>
> When I used your LIKE.... expression then all results from the table show
> rather than just the values selected in the multiple select list box.
>
> To further explain what I've done - I have a listbox which is named
> "Location" on a form currently saved as Form1
>
> What I tried to do with your parameter in my query was this..
>
> Where ("," & [forms]![form1]![location] & ",")
>
> Thanks for the help!
>
> "Michel Walsh" wrote:
>
>> You loop over the Selected items, concatenate the values to be compared
>> (not
>> necessary the values displayed) as a delimited list and use that list as
>> 'argument'. You can also use the operator LIKE instead of InStr so your
>> query can be used outside Access (such as from Excel):
>>
>> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
>> &
>> "*")
>>
>>
>> which assumes your delimited list of value has no unwanted spaces, ie,
>> like
>> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>>
>>
>>
>> Vanderghast, Access MVP
>>
>>
>>
>> "AJ" <AJ@discussions.microsoft.com> wrote in message
>> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
>> > I'm working on this in Access 2007 and i know these posts happened a
>> > while
>> > ago - but how would the parameter be set up from the multi-select list?
>> >
>> > I'm not finding the results that i'm looking for.
>> >
>> > Thanks for the help
>> >
>> > AJ
>> >
>> > "Tom Ellison" wrote:
>> >
>> >> Dear Norm:
>> >>
>> >> It is possible to do this using a parameter, although it isn't the
>> >> best technique.
>> >>
>> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
>> >> example, separated by commas and no spaces, then you can search that
>> >> string with the InStr function using the values in the subject column.
>> >> This can work as long as there are no commas within the subject
>> >> column's values.
>> >>
>> >> To do this, append a comma before and after the parameter list and
>> >> before and after the value from the column:
>> >>
>> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
>> >>
>> >> This places a burden on the user to use commas (without spaces)
>> >> exactly, and to spell each item exactly. I have found this is often
>> >> too much to ask. Instead, a multi-select list box on a form is much
>> >> easier for the users, although it is a bit of work for you to
>> >> implement.
>> >>
>> >> Tom Ellison
>> >> Microsoft Access MVP
>> >> Ellison Enterprises - Your One Stop IT Experts
>> >>
>> >>
>> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
>> >> <NormLundquist@discussions.microsoft.com> wrote:
>> >>
>> >> >Is it possible to have parameter query that uses multiple values?
>> >> >
>> >> >Example: a large database contains a field named "ClassCode". The
>> >> >user
>> >> >wants to specify any number of values for this field (like "JSB",
>> >> >"JSC",
>> >> >"JGS") or retrieve all records.
>> >> >
>> >> >The only parameter query that I get to work only returns 1 value. Is
>> >> >it
>> >> >possible to retrieve multiple values? If so, what is the syntax?
>> >> >
>> >> >Thank You!
>> >>
>> >>
>>
>>
>>

Was this post helpful to you?

Top

5/23/2008 10:44 AM PST

Thank you very much Michel - I'm getting closer

I have gotten it to work where, when the query is run, the parameter asks
for values -

Now how do i take it the next step and rather than typing in the names of
the cities - how do i get it to pull the highlighted cities of the
multiselect list box called Location which is currently on Form1

If its like a normal parameter i presume that i need to somewhere enter

[forms]![form1]![location]

Thank you for your assistance, it is greatly helping.

"Michel Walsh" wrote:

> You have to be in SQL view, to be easier.
>
> Your query should look like:
>
>
>
> SELECT ...
> FROM ...
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn &
> "*")
>
>
>
> Vanderghast, Access MVP
>
>
>
> "AJ" <AJ@discussions.microsoft.com> wrote in message
> news:D4BBCB31-C504-4D77-9EB5-CEB8ACE2A432@microsoft.com...
> > Thanks for the prompt response Michel -
> > When I use the where expression that you gave I receive the error of
> > "Undefined function 'where' in expression"
> >
> > When I used your LIKE.... expression then all results from the table show
> > rather than just the values selected in the multiple select list box.
> >
> > To further explain what I've done - I have a listbox which is named
> > "Location" on a form currently saved as Form1
> >
> > What I tried to do with your parameter in my query was this..
> >
> > Where ("," & [forms]![form1]![location] & ",")
> >
> > Thanks for the help!
> >
> > "Michel Walsh" wrote:
> >
> >> You loop over the Selected items, concatenate the values to be compared
> >> (not
> >> necessary the values displayed) as a delimited list and use that list as
> >> 'argument'. You can also use the operator LIKE instead of InStr so your
> >> query can be used outside Access (such as from Excel):
> >>
> >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
> >> &
> >> "*")
> >>
> >>
> >> which assumes your delimited list of value has no unwanted spaces, ie,
> >> like
> >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
> >>
> >>
> >>
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >> "AJ" <AJ@discussions.microsoft.com> wrote in message
> >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> >> > I'm working on this in Access 2007 and i know these posts happened a
> >> > while
> >> > ago - but how would the parameter be set up from the multi-select list?
> >> >
> >> > I'm not finding the results that i'm looking for.
> >> >
> >> > Thanks for the help
> >> >
> >> > AJ
> >> >
> >> > "Tom Ellison" wrote:
> >> >
> >> >> Dear Norm:
> >> >>
> >> >> It is possible to do this using a parameter, although it isn't the
> >> >> best technique.
> >> >>
> >> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
> >> >> example, separated by commas and no spaces, then you can search that
> >> >> string with the InStr function using the values in the subject column.
> >> >> This can work as long as there are no commas within the subject
> >> >> column's values.
> >> >>
> >> >> To do this, append a comma before and after the parameter list and
> >> >> before and after the value from the column:
> >> >>
> >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
> >> >>
> >> >> This places a burden on the user to use commas (without spaces)
> >> >> exactly, and to spell each item exactly. I have found this is often
> >> >> too much to ask. Instead, a multi-select list box on a form is much
> >> >> easier for the users, although it is a bit of work for you to
> >> >> implement.
> >> >>
> >> >> Tom Ellison
> >> >> Microsoft Access MVP
> >> >> Ellison Enterprises - Your One Stop IT Experts
> >> >>
> >> >>
> >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> >> >> <NormLundquist@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Is it possible to have parameter query that uses multiple values?
> >> >> >
> >> >> >Example: a large database contains a field named "ClassCode". The
> >> >> >user
> >> >> >wants to specify any number of values for this field (like "JSB",
> >> >> >"JSC",
> >> >> >"JGS") or retrieve all records.
> >> >> >
> >> >> >The only parameter query that I get to work only returns 1 value. Is
> >> >> >it
> >> >> >possible to retrieve multiple values? If so, what is the syntax?
> >> >> >
> >> >> >Thank You!
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Was this post helpful to you?

Top

Michel Walsh

5/23/2008 10:56 AM PST

A multi select list box does not have a 'value', so you cannot use
FORMS!formName!ListBoxControlName

You have to built the string made of the concatenation of the selected
values.

You can also use the technique illustrated at
http://www.mvps.org/access/forms/frm0007.htm, or take its idea to built not
"field=2 OR field=3 OR field=4" , as the example does, but the string
"2,3,4"

Hoping it may help,
Vanderghast, Access MVP

"AJ" <AJ@discussions.microsoft.com> wrote in message
news:79629BF0-0D4F-4A43-8614-6B6ABF090B8D@microsoft.com...
> Thank you very much Michel - I'm getting closer
>
> I have gotten it to work where, when the query is run, the parameter asks
> for values -
>
> Now how do i take it the next step and rather than typing in the names of
> the cities - how do i get it to pull the highlighted cities of the
> multiselect list box called Location which is currently on Form1
>
> If its like a normal parameter i presume that i need to somewhere enter
>
> [forms]![form1]![location]
>
> Thank you for your assistance, it is greatly helping.
>
> "Michel Walsh" wrote:
>
>> You have to be in SQL view, to be easier.
>>
>> Your query should look like:
>>
>>
>>
>> SELECT ...
>> FROM ...
>> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
>> &
>> "*")
>>
>>
>>
>> Vanderghast, Access MVP
>>
>>
>>
>> "AJ" <AJ@discussions.microsoft.com> wrote in message
>> news:D4BBCB31-C504-4D77-9EB5-CEB8ACE2A432@microsoft.com...
>> > Thanks for the prompt response Michel -
>> > When I use the where expression that you gave I receive the error of
>> > "Undefined function 'where' in expression"
>> >
>> > When I used your LIKE.... expression then all results from the table
>> > show
>> > rather than just the values selected in the multiple select list box.
>> >
>> > To further explain what I've done - I have a listbox which is named
>> > "Location" on a form currently saved as Form1
>> >
>> > What I tried to do with your parameter in my query was this..
>> >
>> > Where ("," & [forms]![form1]![location] & ",")
>> >
>> > Thanks for the help!
>> >
>> > "Michel Walsh" wrote:
>> >
>> >> You loop over the Selected items, concatenate the values to be
>> >> compared
>> >> (not
>> >> necessary the values displayed) as a delimited list and use that list
>> >> as
>> >> 'argument'. You can also use the operator LIKE instead of InStr so
>> >> your
>> >> query can be used outside Access (such as from Excel):
>> >>
>> >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" &
>> >> someColumn
>> >> &
>> >> "*")
>> >>
>> >>
>> >> which assumes your delimited list of value has no unwanted spaces, ie,
>> >> like
>> >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>> >>
>> >>
>> >>
>> >> Vanderghast, Access MVP
>> >>
>> >>
>> >>
>> >> "AJ" <AJ@discussions.microsoft.com> wrote in message
>> >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
>> >> > I'm working on this in Access 2007 and i know these posts happened a
>> >> > while
>> >> > ago - but how would the parameter be set up from the multi-select
>> >> > list?
>> >> >
>> >> > I'm not finding the results that i'm looking for.
>> >> >
>> >> > Thanks for the help
>> >> >
>> >> > AJ
>> >> >
>> >> > "Tom Ellison" wrote:
>> >> >
>> >> >> Dear Norm:
>> >> >>
>> >> >> It is possible to do this using a parameter, although it isn't the
>> >> >> best technique.
>> >> >>
>> >> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
>> >> >> example, separated by commas and no spaces, then you can search
>> >> >> that
>> >> >> string with the InStr function using the values in the subject
>> >> >> column.
>> >> >> This can work as long as there are no commas within the subject
>> >> >> column's values.
>> >> >>
>> >> >> To do this, append a comma before and after the parameter list and
>> >> >> before and after the value from the column:
>> >> >>
>> >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") >
>> >> >> 0
>> >> >>
>> >> >> This places a burden on the user to use commas (without spaces)
>> >> >> exactly, and to spell each item exactly. I have found this is
>> >> >> often
>> >> >> too much to ask. Instead, a multi-select list box on a form is
>> >> >> much
>> >> >> easier for the users, although it is a bit of work for you to
>> >> >> implement.
>> >> >>
>> >> >> Tom Ellison
>> >> >> Microsoft Access MVP
>> >> >> Ellison Enterprises - Your One Stop IT Experts
>> >> >>
>> >> >>
>> >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
>> >> >> <NormLundquist@discussions.microsoft.com> wrote:
>> >> >>
>> >> >> >Is it possible to have parameter query that uses multiple values?
>> >> >> >
>> >> >> >Example: a large database contains a field named "ClassCode". The
>> >> >> >user
>> >> >> >wants to specify any number of values for this field (like "JSB",
>> >> >> >"JSC",
>> >> >> >"JGS") or retrieve all records.
>> >> >> >
>> >> >> >The only parameter query that I get to work only returns 1 value.
>> >> >> >Is
>> >> >> >it
>> >> >> >possible to retrieve multiple values? If so, what is the syntax?
>> >> >> >
>> >> >> >Thank You!
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Was this post helpful to you?

Top

Jacqueline

12/30/2008 1:24 PM PST

Hi Michel,
You posted this a while back, can I pick your brains a little here please. :)

I would like to create an option where a student can click a button and run
multipal check off sheets based on the classes they enter. The form would be
run from a parameter query that pulls the tasks associated with the courses
they enter.

Example the student would input VET102, VET103 etc. I have found that
parameter queries will allow you two input options but I am sure more than
that will be needed. Is this possible am I going about it the hard way (I
have propensity for the hard way) :)
Any help would be greatly appreciated.
Thanks

--
Jacqueline

"Michel Walsh" wrote:

> You loop over the Selected items, concatenate the values to be compared (not
> necessary the values displayed) as a delimited list and use that list as
> 'argument'. You can also use the operator LIKE instead of InStr so your
> query can be used outside Access (such as from Excel):
>
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn &
> "*")
>
>
> which assumes your delimited list of value has no unwanted spaces, ie, like
> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>
>
>
> Vanderghast, Access MVP
>
>
>
> "AJ" <AJ@discussions.microsoft.com> wrote in message
> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> > I'm working on this in Access 2007 and i know these posts happened a while
> > ago - but how would the parameter be set up from the multi-select list?
> >
> > I'm not finding the results that i'm looking for.
> >
> > Thanks for the help
> >
> > AJ
> >
> > "Tom Ellison" wrote:
> >
> >> Dear Norm:
> >>
> >> It is possible to do this using a parameter, although it isn't the
> >> best technique.
> >>
> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
> >> example, separated by commas and no spaces, then you can search that
> >> string with the InStr function using the values in the subject column.
> >> This can work as long as there are no commas within the subject
> >> column's values.
> >>
> >> To do this, append a comma before and after the parameter list and
> >> before and after the value from the column:
> >>
> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
> >>
> >> This places a burden on the user to use commas (without spaces)
> >> exactly, and to spell each item exactly. I have found this is often
> >> too much to ask. Instead, a multi-select list box on a form is much
> >> easier for the users, although it is a bit of work for you to
> >> implement.
> >>
> >> Tom Ellison
> >> Microsoft Access MVP
> >> Ellison Enterprises - Your One Stop IT Experts
> >>
> >>
> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> >> <NormLundquist@discussions.microsoft.com> wrote:
> >>
> >> >Is it possible to have parameter query that uses multiple values?
> >> >
> >> >Example: a large database contains a field named "ClassCode". The user
> >> >wants to specify any number of values for this field (like "JSB", "JSC",
> >> >"JGS") or retrieve all records.
> >> >
> >> >The only parameter query that I get to work only returns 1 value. Is it
> >> >possible to retrieve multiple values? If so, what is the syntax?
> >> >
> >> >Thank You!
> >>
> >>
>
>
>

Was this post helpful to you?

Top

Michel Walsh

1/5/2009 8:21 AM PST

At first glance, it seems that if the student enter

Vet102,Vet103

(no space, just a coma between the names), as answer to the prompt for
[delimitedListParam] (or other mechanic you really use), that should do
the job with:

WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*," & someColumn &
",*")

(note the addition of coma)

To allow the student to use space in the input,

Vet102, Vet103

you can try using:

WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" &
someColumn & "[, ]*")

Vanderghast, Access MVP

"Jacqueline" <jstaley@yvcc.edu> wrote in message
news:86009158-39C4-4399-9539-AAE72055DB3A@microsoft.com...
> Hi Michel,
> You posted this a while back, can I pick your brains a little here please.
> :)
>
> I would like to create an option where a student can click a button and
> run
> multipal check off sheets based on the classes they enter. The form would
> be
> run from a parameter query that pulls the tasks associated with the
> courses
> they enter.
>
> Example the student would input VET102, VET103 etc. I have found that
> parameter queries will allow you two input options but I am sure more than
> that will be needed. Is this possible am I going about it the hard way (I
> have propensity for the hard way) :)
> Any help would be greatly appreciated.
> Thanks
>
> --
> Jacqueline
>
>
> "Michel Walsh" wrote:
>
>> You loop over the Selected items, concatenate the values to be compared
>> (not
>> necessary the values displayed) as a delimited list and use that list as
>> 'argument'. You can also use the operator LIKE instead of InStr so your
>> query can be used outside Access (such as from Excel):
>>
>> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
>> &
>> "*")
>>
>>
>> which assumes your delimited list of value has no unwanted spaces, ie,
>> like
>> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>>
>>
>>
>> Vanderghast, Access MVP
>>
>>
>>
>> "AJ" <AJ@discussions.microsoft.com> wrote in message
>> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
>> > I'm working on this in Access 2007 and i know these posts happened a
>> > while
>> > ago - but how would the parameter be set up from the multi-select list?
>> >
>> > I'm not finding the results that i'm looking for.
>> >
>> > Thanks for the help
>> >
>> > AJ
>> >
>> > "Tom Ellison" wrote:
>> >
>> >> Dear Norm:
>> >>
>> >> It is possible to do this using a parameter, although it isn't the
>> >> best technique.
>> >>
>> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
>> >> example, separated by commas and no spaces, then you can search that
>> >> string with the InStr function using the values in the subject column.
>> >> This can work as long as there are no commas within the subject
>> >> column's values.
>> >>
>> >> To do this, append a comma before and after the parameter list and
>> >> before and after the value from the column:
>> >>
>> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
>> >>
>> >> This places a burden on the user to use commas (without spaces)
>> >> exactly, and to spell each item exactly. I have found this is often
>> >> too much to ask. Instead, a multi-select list box on a form is much
>> >> easier for the users, although it is a bit of work for you to
>> >> implement.
>> >>
>> >> Tom Ellison
>> >> Microsoft Access MVP
>> >> Ellison Enterprises - Your One Stop IT Experts
>> >>
>> >>
>> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
>> >> <NormLundquist@discussions.microsoft.com> wrote:
>> >>
>> >> >Is it possible to have parameter query that uses multiple values?
>> >> >
>> >> >Example: a large database contains a field named "ClassCode". The
>> >> >user
>> >> >wants to specify any number of values for this field (like "JSB",
>> >> >"JSC",
>> >> >"JGS") or retrieve all records.
>> >> >
>> >> >The only parameter query that I get to work only returns 1 value. Is
>> >> >it
>> >> >possible to retrieve multiple values? If so, what is the syntax?
>> >> >
>> >> >Thank You!
>> >>
>> >>
>>
>>
>>

Was this post helpful to you?

Top

Jacqueline

1/5/2009 8:30 AM PST

Thanks , I will give this a try.
--
Jacqueline

"Michel Walsh" wrote:

> At first glance, it seems that if the student enter
>
> Vet102,Vet103
>
> (no space, just a coma between the names), as answer to the prompt for
> [delimitedListParam] (or other mechanic you really use), that should do
> the job with:
>
>
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*," & someColumn &
> ",*")
>
>
> (note the addition of coma)
>
>
>
> To allow the student to use space in the input,
>
> Vet102, Vet103
>
>
> you can try using:
>
>
>
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" &
> someColumn & "[, ]*")
>
>
>
> Vanderghast, Access MVP
>
>
>
> "Jacqueline" <jstaley@yvcc.edu> wrote in message
> news:86009158-39C4-4399-9539-AAE72055DB3A@microsoft.com...
> > Hi Michel,
> > You posted this a while back, can I pick your brains a little here please.
> > :)
> >
> > I would like to create an option where a student can click a button and
> > run
> > multipal check off sheets based on the classes they enter. The form would
> > be
> > run from a parameter query that pulls the tasks associated with the
> > courses
> > they enter.
> >
> > Example the student would input VET102, VET103 etc. I have found that
> > parameter queries will allow you two input options but I am sure more than
> > that will be needed. Is this possible am I going about it the hard way (I
> > have propensity for the hard way) :)
> > Any help would be greatly appreciated.
> > Thanks
> >
> > --
> > Jacqueline
> >
> >
> > "Michel Walsh" wrote:
> >
> >> You loop over the Selected items, concatenate the values to be compared
> >> (not
> >> necessary the values displayed) as a delimited list and use that list as
> >> 'argument'. You can also use the operator LIKE instead of InStr so your
> >> query can be used outside Access (such as from Excel):
> >>
> >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
> >> &
> >> "*")
> >>
> >>
> >> which assumes your delimited list of value has no unwanted spaces, ie,
> >> like
> >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
> >>
> >>
> >>
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >> "AJ" <AJ@discussions.microsoft.com> wrote in message
> >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> >> > I'm working on this in Access 2007 and i know these posts happened a
> >> > while
> >> > ago - but how would the parameter be set up from the multi-select list?
> >> >
> >> > I'm not finding the results that i'm looking for.
> >> >
> >> > Thanks for the help
> >> >
> >> > AJ
> >> >
> >> > "Tom Ellison" wrote:
> >> >
> >> >> Dear Norm:
> >> >>
> >> >> It is possible to do this using a parameter, although it isn't the
> >> >> best technique.
> >> >>
> >> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
> >> >> example, separated by commas and no spaces, then you can search that
> >> >> string with the InStr function using the values in the subject column.
> >> >> This can work as long as there are no commas within the subject
> >> >> column's values.
> >> >>
> >> >> To do this, append a comma before and after the parameter list and
> >> >> before and after the value from the column:
> >> >>
> >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
> >> >>
> >> >> This places a burden on the user to use commas (without spaces)
> >> >> exactly, and to spell each item exactly. I have found this is often
> >> >> too much to ask. Instead, a multi-select list box on a form is much
> >> >> easier for the users, although it is a bit of work for you to
> >> >> implement.
> >> >>
> >> >> Tom Ellison
> >> >> Microsoft Access MVP
> >> >> Ellison Enterprises - Your One Stop IT Experts
> >> >>
> >> >>
> >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> >> >> <NormLundquist@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Is it possible to have parameter query that uses multiple values?
> >> >> >
> >> >> >Example: a large database contains a field named "ClassCode". The
> >> >> >user
> >> >> >wants to specify any number of values for this field (like "JSB",
> >> >> >"JSC",
> >> >> >"JGS") or retrieve all records.
> >> >> >
> >> >> >The only parameter query that I get to work only returns 1 value. Is
> >> >> >it
> >> >> >possible to retrieve multiple values? If so, what is the syntax?
> >> >> >
> >> >> >Thank You!
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Was this post helpful to you?

Top

Jacqueline

1/12/2009 1:51 PM PST

Michel,
I am finally able to get back to this project, and tried your code but it is
not working at all, I keep getting the where error.

Here is the code you game me:

WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" & someColumn & "[,
]*")

In the query the instructor will be prompted to enter the course or courses,
Vet102,vet103 etc.

I do not have a list box built on the form, but do have a lookup table that
lists all the courses. I guess I am confused bucause when you run a normal
parameter query the field you place the [] is the question (in this case
Course), but your code looks like it needs a seperat Where statement.

By the way, this will be used in an append query to append courses to a
student cohort.

I hope this is clear, let me know if you have any ideas.
--
Jacqueline

"Jacqueline" wrote:

> Thanks , I will give this a try.
> --
> Jacqueline
>
>
> "Michel Walsh" wrote:
>
> > At first glance, it seems that if the student enter
> >
> > Vet102,Vet103
> >
> > (no space, just a coma between the names), as answer to the prompt for
> > [delimitedListParam] (or other mechanic you really use), that should do
> > the job with:
> >
> >
> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*," & someColumn &
> > ",*")
> >
> >
> > (note the addition of coma)
> >
> >
> >
> > To allow the student to use space in the input,
> >
> > Vet102, Vet103
> >
> >
> > you can try using:
> >
> >
> >
> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" &
> > someColumn & "[, ]*")
> >
> >
> >
> > Vanderghast, Access MVP
> >
> >
> >
> > "Jacqueline" <jstaley@yvcc.edu> wrote in message
> > news:86009158-39C4-4399-9539-AAE72055DB3A@microsoft.com...
> > > Hi Michel,
> > > You posted this a while back, can I pick your brains a little here please.
> > > :)
> > >
> > > I would like to create an option where a student can click a button and
> > > run
> > > multipal check off sheets based on the classes they enter. The form would
> > > be
> > > run from a parameter query that pulls the tasks associated with the
> > > courses
> > > they enter.
> > >
> > > Example the student would input VET102, VET103 etc. I have found that
> > > parameter queries will allow you two input options but I am sure more than
> > > that will be needed. Is this possible am I going about it the hard way (I
> > > have propensity for the hard way) :)
> > > Any help would be greatly appreciated.
> > > Thanks
> > >
> > > --
> > > Jacqueline
> > >
> > >
> > > "Michel Walsh" wrote:
> > >
> > >> You loop over the Selected items, concatenate the values to be compared
> > >> (not
> > >> necessary the values displayed) as a delimited list and use that list as
> > >> 'argument'. You can also use the operator LIKE instead of InStr so your
> > >> query can be used outside Access (such as from Excel):
> > >>
> > >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" & someColumn
> > >> &
> > >> "*")
> > >>
> > >>
> > >> which assumes your delimited list of value has no unwanted spaces, ie,
> > >> like
> > >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
> > >>
> > >>
> > >>
> > >> Vanderghast, Access MVP
> > >>
> > >>
> > >>
> > >> "AJ" <AJ@discussions.microsoft.com> wrote in message
> > >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> > >> > I'm working on this in Access 2007 and i know these posts happened a
> > >> > while
> > >> > ago - but how would the parameter be set up from the multi-select list?
> > >> >
> > >> > I'm not finding the results that i'm looking for.
> > >> >
> > >> > Thanks for the help
> > >> >
> > >> > AJ
> > >> >
> > >> > "Tom Ellison" wrote:
> > >> >
> > >> >> Dear Norm:
> > >> >>
> > >> >> It is possible to do this using a parameter, although it isn't the
> > >> >> best technique.
> > >> >>
> > >> >> If the user enters a list like JSB,JSC,JGS (without quotes in this
> > >> >> example, separated by commas and no spaces, then you can search that
> > >> >> string with the InStr function using the values in the subject column.
> > >> >> This can work as long as there are no commas within the subject
> > >> >> column's values.
> > >> >>
> > >> >> To do this, append a comma before and after the parameter list and
> > >> >> before and after the value from the column:
> > >> >>
> > >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",") > 0
> > >> >>
> > >> >> This places a burden on the user to use commas (without spaces)
> > >> >> exactly, and to spell each item exactly. I have found this is often
> > >> >> too much to ask. Instead, a multi-select list box on a form is much
> > >> >> easier for the users, although it is a bit of work for you to
> > >> >> implement.
> > >> >>
> > >> >> Tom Ellison
> > >> >> Microsoft Access MVP
> > >> >> Ellison Enterprises - Your One Stop IT Experts
> > >> >>
> > >> >>
> > >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> > >> >> <NormLundquist@discussions.microsoft.com> wrote:
> > >> >>
> > >> >> >Is it possible to have parameter query that uses multiple values?
> > >> >> >
> > >> >> >Example: a large database contains a field named "ClassCode". The
> > >> >> >user
> > >> >> >wants to specify any number of values for this field (like "JSB",
> > >> >> >"JSC",
> > >> >> >"JGS") or retrieve all records.
> > >> >> >
> > >> >> >The only parameter query that I get to work only returns 1 value. Is
> > >> >> >it
> > >> >> >possible to retrieve multiple values? If so, what is the syntax?
> > >> >> >
> > >> >> >Thank You!
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Was this post helpful to you?

Top

Michel Walsh

1/13/2009 5:40 AM PST

Maybe there is a problem of context. If you try, in Northwind:

SELECT Customers.*
FROM Customers
WHERE ("," & [Enter cities] & ",") LIKE "*[, ]" & [City] & "[, ]*"

and when you run the query, supply, to the prompt:

London, Paris, Berlin

you should get all the relevant records, from table Customers.

Vanderghast, Access MVP

"Jacqueline" <jstaley@yvcc.edu> wrote in message
news:DDF5BDC3-DEB8-44AB-B184-55BA3935ADC4@microsoft.com...
> Michel,
> I am finally able to get back to this project, and tried your code but it
> is
> not working at all, I keep getting the where error.
>
> Here is the code you game me:
>
> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" & someColumn &
> "[,
> ]*")
>
> In the query the instructor will be prompted to enter the course or
> courses,
> Vet102,vet103 etc.
>
> I do not have a list box built on the form, but do have a lookup table
> that
> lists all the courses. I guess I am confused bucause when you run a normal
> parameter query the field you place the [] is the question (in this case
> Course), but your code looks like it needs a seperat Where statement.
>
> By the way, this will be used in an append query to append courses to a
> student cohort.
>
> I hope this is clear, let me know if you have any ideas.
> --
> Jacqueline
>
>
> "Jacqueline" wrote:
>
>> Thanks , I will give this a try.
>> --
>> Jacqueline
>>
>>
>> "Michel Walsh" wrote:
>>
>> > At first glance, it seems that if the student enter
>> >
>> > Vet102,Vet103
>> >
>> > (no space, just a coma between the names), as answer to the prompt for
>> > [delimitedListParam] (or other mechanic you really use), that should
>> > do
>> > the job with:
>> >
>> >
>> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*," &
>> > someColumn &
>> > ",*")
>> >
>> >
>> > (note the addition of coma)
>> >
>> >
>> >
>> > To allow the student to use space in the input,
>> >
>> > Vet102, Vet103
>> >
>> >
>> > you can try using:
>> >
>> >
>> >
>> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" &
>> > someColumn & "[, ]*")
>> >
>> >
>> >
>> > Vanderghast, Access MVP
>> >
>> >
>> >
>> > "Jacqueline" <jstaley@yvcc.edu> wrote in message
>> > news:86009158-39C4-4399-9539-AAE72055DB3A@microsoft.com...
>> > > Hi Michel,
>> > > You posted this a while back, can I pick your brains a little here
>> > > please.
>> > > :)
>> > >
>> > > I would like to create an option where a student can click a button
>> > > and
>> > > run
>> > > multipal check off sheets based on the classes they enter. The form
>> > > would
>> > > be
>> > > run from a parameter query that pulls the tasks associated with the
>> > > courses
>> > > they enter.
>> > >
>> > > Example the student would input VET102, VET103 etc. I have found that
>> > > parameter queries will allow you two input options but I am sure more
>> > > than
>> > > that will be needed. Is this possible am I going about it the hard
>> > > way (I
>> > > have propensity for the hard way) :)
>> > > Any help would be greatly appreciated.
>> > > Thanks
>> > >
>> > > --
>> > > Jacqueline
>> > >
>> > >
>> > > "Michel Walsh" wrote:
>> > >
>> > >> You loop over the Selected items, concatenate the values to be
>> > >> compared
>> > >> (not
>> > >> necessary the values displayed) as a delimited list and use that
>> > >> list as
>> > >> 'argument'. You can also use the operator LIKE instead of InStr so
>> > >> your
>> > >> query can be used outside Access (such as from Excel):
>> > >>
>> > >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" &
>> > >> someColumn
>> > >> &
>> > >> "*")
>> > >>
>> > >>
>> > >> which assumes your delimited list of value has no unwanted spaces,
>> > >> ie,
>> > >> like
>> > >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
>> > >>
>> > >>
>> > >>
>> > >> Vanderghast, Access MVP
>> > >>
>> > >>
>> > >>
>> > >> "AJ" <AJ@discussions.microsoft.com> wrote in message
>> > >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
>> > >> > I'm working on this in Access 2007 and i know these posts happened
>> > >> > a
>> > >> > while
>> > >> > ago - but how would the parameter be set up from the multi-select
>> > >> > list?
>> > >> >
>> > >> > I'm not finding the results that i'm looking for.
>> > >> >
>> > >> > Thanks for the help
>> > >> >
>> > >> > AJ
>> > >> >
>> > >> > "Tom Ellison" wrote:
>> > >> >
>> > >> >> Dear Norm:
>> > >> >>
>> > >> >> It is possible to do this using a parameter, although it isn't
>> > >> >> the
>> > >> >> best technique.
>> > >> >>
>> > >> >> If the user enters a list like JSB,JSC,JGS (without quotes in
>> > >> >> this
>> > >> >> example, separated by commas and no spaces, then you can search
>> > >> >> that
>> > >> >> string with the InStr function using the values in the subject
>> > >> >> column.
>> > >> >> This can work as long as there are no commas within the subject
>> > >> >> column's values.
>> > >> >>
>> > >> >> To do this, append a comma before and after the parameter list
>> > >> >> and
>> > >> >> before and after the value from the column:
>> > >> >>
>> > >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",")
>> > >> >> > 0
>> > >> >>
>> > >> >> This places a burden on the user to use commas (without spaces)
>> > >> >> exactly, and to spell each item exactly. I have found this is
>> > >> >> often
>> > >> >> too much to ask. Instead, a multi-select list box on a form is
>> > >> >> much
>> > >> >> easier for the users, although it is a bit of work for you to
>> > >> >> implement.
>> > >> >>
>> > >> >> Tom Ellison
>> > >> >> Microsoft Access MVP
>> > >> >> Ellison Enterprises - Your One Stop IT Experts
>> > >> >>
>> > >> >>
>> > >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
>> > >> >> <NormLundquist@discussions.microsoft.com> wrote:
>> > >> >>
>> > >> >> >Is it possible to have parameter query that uses multiple
>> > >> >> >values?
>> > >> >> >
>> > >> >> >Example: a large database contains a field named "ClassCode".
>> > >> >> >The
>> > >> >> >user
>> > >> >> >wants to specify any number of values for this field (like
>> > >> >> >"JSB",
>> > >> >> >"JSC",
>> > >> >> >"JGS") or retrieve all records.
>> > >> >> >
>> > >> >> >The only parameter query that I get to work only returns 1
>> > >> >> >value. Is
>> > >> >> >it
>> > >> >> >possible to retrieve multiple values? If so, what is the
>> > >> >> >syntax?
>> > >> >> >
>> > >> >> >Thank You!
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

Was this post helpful to you?

Top

Jacqueline

1/13/2009 8:58 AM PST

This worked, thanks..
--
Jacqueline

"Michel Walsh" wrote:

> Maybe there is a problem of context. If you try, in Northwind:
>
> SELECT Customers.*
> FROM Customers
> WHERE ("," & [Enter cities] & ",") LIKE "*[, ]" & [City] & "[, ]*"
>
>
>
> and when you run the query, supply, to the prompt:
>
> London, Paris, Berlin
>
>
> you should get all the relevant records, from table Customers.
>
>
>
> Vanderghast, Access MVP
>
>
>
>
> "Jacqueline" <jstaley@yvcc.edu> wrote in message
> news:DDF5BDC3-DEB8-44AB-B184-55BA3935ADC4@microsoft.com...
> > Michel,
> > I am finally able to get back to this project, and tried your code but it
> > is
> > not working at all, I keep getting the where error.
> >
> > Here is the code you game me:
> >
> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" & someColumn &
> > "[,
> > ]*")
> >
> > In the query the instructor will be prompted to enter the course or
> > courses,
> > Vet102,vet103 etc.
> >
> > I do not have a list box built on the form, but do have a lookup table
> > that
> > lists all the courses. I guess I am confused bucause when you run a normal
> > parameter query the field you place the [] is the question (in this case
> > Course), but your code looks like it needs a seperat Where statement.
> >
> > By the way, this will be used in an append query to append courses to a
> > student cohort.
> >
> > I hope this is clear, let me know if you have any ideas.
> > --
> > Jacqueline
> >
> >
> > "Jacqueline" wrote:
> >
> >> Thanks , I will give this a try.
> >> --
> >> Jacqueline
> >>
> >>
> >> "Michel Walsh" wrote:
> >>
> >> > At first glance, it seems that if the student enter
> >> >
> >> > Vet102,Vet103
> >> >
> >> > (no space, just a coma between the names), as answer to the prompt for
> >> > [delimitedListParam] (or other mechanic you really use), that should
> >> > do
> >> > the job with:
> >> >
> >> >
> >> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*," &
> >> > someColumn &
> >> > ",*")
> >> >
> >> >
> >> > (note the addition of coma)
> >> >
> >> >
> >> >
> >> > To allow the student to use space in the input,
> >> >
> >> > Vet102, Vet103
> >> >
> >> >
> >> > you can try using:
> >> >
> >> >
> >> >
> >> > WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*[, ]" &
> >> > someColumn & "[, ]*")
> >> >
> >> >
> >> >
> >> > Vanderghast, Access MVP
> >> >
> >> >
> >> >
> >> > "Jacqueline" <jstaley@yvcc.edu> wrote in message
> >> > news:86009158-39C4-4399-9539-AAE72055DB3A@microsoft.com...
> >> > > Hi Michel,
> >> > > You posted this a while back, can I pick your brains a little here
> >> > > please.
> >> > > :)
> >> > >
> >> > > I would like to create an option where a student can click a button
> >> > > and
> >> > > run
> >> > > multipal check off sheets based on the classes they enter. The form
> >> > > would
> >> > > be
> >> > > run from a parameter query that pulls the tasks associated with the
> >> > > courses
> >> > > they enter.
> >> > >
> >> > > Example the student would input VET102, VET103 etc. I have found that
> >> > > parameter queries will allow you two input options but I am sure more
> >> > > than
> >> > > that will be needed. Is this possible am I going about it the hard
> >> > > way (I
> >> > > have propensity for the hard way) :)
> >> > > Any help would be greatly appreciated.
> >> > > Thanks
> >> > >
> >> > > --
> >> > > Jacqueline
> >> > >
> >> > >
> >> > > "Michel Walsh" wrote:
> >> > >
> >> > >> You loop over the Selected items, concatenate the values to be
> >> > >> compared
> >> > >> (not
> >> > >> necessary the values displayed) as a delimited list and use that
> >> > >> list as
> >> > >> 'argument'. You can also use the operator LIKE instead of InStr so
> >> > >> your
> >> > >> query can be used outside Access (such as from Excel):
> >> > >>
> >> > >> WHERE ( "," & [delimitedListParam] & ", " ) LIKE ("*" &
> >> > >> someColumn
> >> > >> &
> >> > >> "*")
> >> > >>
> >> > >>
> >> > >> which assumes your delimited list of value has no unwanted spaces,
> >> > >> ie,
> >> > >> like
> >> > >> "1,2,3,4" and not "1, 2, 3, 4" having a space after each coma.
> >> > >>
> >> > >>
> >> > >>
> >> > >> Vanderghast, Access MVP
> >> > >>
> >> > >>
> >> > >>
> >> > >> "AJ" <AJ@discussions.microsoft.com> wrote in message
> >> > >> news:A5CBA9F4-2587-4069-A80A-5C2BAFDA992B@microsoft.com...
> >> > >> > I'm working on this in Access 2007 and i know these posts happened
> >> > >> > a
> >> > >> > while
> >> > >> > ago - but how would the parameter be set up from the multi-select
> >> > >> > list?
> >> > >> >
> >> > >> > I'm not finding the results that i'm looking for.
> >> > >> >
> >> > >> > Thanks for the help
> >> > >> >
> >> > >> > AJ
> >> > >> >
> >> > >> > "Tom Ellison" wrote:
> >> > >> >
> >> > >> >> Dear Norm:
> >> > >> >>
> >> > >> >> It is possible to do this using a parameter, although it isn't
> >> > >> >> the
> >> > >> >> best technique.
> >> > >> >>
> >> > >> >> If the user enters a list like JSB,JSC,JGS (without quotes in
> >> > >> >> this
> >> > >> >> example, separated by commas and no spaces, then you can search
> >> > >> >> that
> >> > >> >> string with the InStr function using the values in the subject
> >> > >> >> column.
> >> > >> >> This can work as long as there are no commas within the subject
> >> > >> >> column's values.
> >> > >> >>
> >> > >> >> To do this, append a comma before and after the parameter list
> >> > >> >> and
> >> > >> >> before and after the value from the column:
> >> > >> >>
> >> > >> >> WHERE InStr("," & [Enter List: ] & ",", "," & [SomeColumn] & ",")
> >> > >> >> > 0
> >> > >> >>
> >> > >> >> This places a burden on the user to use commas (without spaces)
> >> > >> >> exactly, and to spell each item exactly. I have found this is
> >> > >> >> often
> >> > >> >> too much to ask. Instead, a multi-select list box on a form is
> >> > >> >> much
> >> > >> >> easier for the users, although it is a bit of work for you to
> >> > >> >> implement.
> >> > >> >>
> >> > >> >> Tom Ellison
> >> > >> >> Microsoft Access MVP
> >> > >> >> Ellison Enterprises - Your One Stop IT Experts
> >> > >> >>
> >> > >> >>
> >> > >> >> On Wed, 27 Oct 2004 10:29:10 -0700, "Norm Lundquist"
> >> > >> >> <NormLundquist@discussions.microsoft.com> wrote:
> >> > >> >>
> >> > >> >> >Is it possible to have parameter query that uses multiple
> >> > >> >> >values?
> >> > >> >> >
> >> > >> >> >Example: a large database contains a field named "ClassCode".
> >> > >> >> >The
> >> > >> >> >user
> >> > >> >> >wants to specify any number of values for this field (like
> >> > >> >> >"JSB",
> >> > >> >> >"JSC",
> >> > >> >> >"JGS") or retrieve all records.
> >> > >> >> >
> >> > >> >> >The only parameter query that I get to work only returns 1
> >> > >> >> >value. Is
> >> > >> >> >it
> >> > >> >> >possible to retrieve multiple values? If so, what is the
> >> > >> >> >syntax?
> >> > >> >> >
> >> > >> >> >Thank You!
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies
			More...