How to use a parameter query with multiple values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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.
 
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
 
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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Thanks , I will give this a try.
--
Jacqueline


Michel Walsh said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Thanks , I will give this a try.
--
Jacqueline


Michel Walsh said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Thanks , I will give this a try.
--
Jacqueline


Michel Walsh said:
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!
Click to expand...
Click to expand...
Click to expand...
 
This worked, thanks..
--
Jacqueline


Michel Walsh said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top