How to use a parameter query with multiple values?

[Guest]

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!

 

[Ken Snell [MVP]]

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.

 

[Tom Ellison]

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

 

[Roger Carlson]

I agree with Tom. Use a multi-select listbox on a form to choose multiple
values. On my website (www.rogersaccesslibrary.com), is a small sample
databases which illustrates how. Look for: "CreateQueries2.mdb".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[AJ]

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

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

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!

 

[Michel Walsh]

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

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

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

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!

 

[AJ]

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!

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

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

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"

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!

 

[Michel Walsh]

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

Your query should look like:



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



Vanderghast, Access MVP

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!

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

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

:

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"

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!

 

[AJ]

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.

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

Your query should look like:



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



Vanderghast, Access MVP

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!

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



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

:

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"

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!

 

[Michel Walsh]

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

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.

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

Your query should look like:



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



Vanderghast, Access MVP

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!

:

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



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

:

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"

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!

 

[Jacqueline]

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

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

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

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"

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!

 

[Michel Walsh]

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

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

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

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

:

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"

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!

 

[Jacqueline]

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

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

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

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



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

:

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"

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!

 

[Jacqueline]

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

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

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

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


:

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



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

:

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"

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!

 

[Michel Walsh]

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

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

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

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



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


:

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



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

:

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"

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!

 

[Jacqueline]

This worked, thanks..
--
Jacqueline

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

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

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


:

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



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


:

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



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

:

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"

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!