Multiselect Listbox as qualifier for query

[Guest]

In Access 97, I'm trying to use the IN clause as a qualifier in a parameter
query so the user can type in more than one value to filter with on a single
field

Hardcoding the criteria to the following works fine...
IN ("FLA", "GA")

I can't get the following to work - any advice?
IN ([ENTER STATES TO FILTER ON SEPARATED BY COMMAS])

The parameter box displays but no matter how I enter the data (quotes; no
quotes; single quotes; double quotes, etc), nothing qualifies if I enter more
than 1 state.

 

[Tom Ellison]

Dear Mike:

There are two approaches to make this work using a listbox with
multiple selections. One is to code the building of the SQL
dynamically so it generates the string

IN ("FLA", "GA")

The other is to have a local table into which you place the selected
state abbreviations and then join to that table.

If you want the users to enter a list as a query parameter, the
requirements on the user will be quite stringent compared with the
list box. Obviously, the user would have to spell each state
abbreviation exactly and never omit a comma. In addition, you must
either have them always put a space between items, after the comma, or
never do so.

You could then use INSTR() to search the string. To do this at all
reliably, assuming the users do not put spaces after commas, the
expression could be:

InStr("," & [Enter States to filter separated by commas] & ",", "," &
StateColumn & ",") <> 0

This would search for

,FLA,

in the string of entered states

,FLA,GA,

This puts quite a burden on the user, and so is inferior to using the
list box.

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

 

[Allen Browne]

If you need an example of how to code Tom's first suggestion, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Mike:

There are two approaches to make this work using a listbox with
multiple selections. One is to code the building of the SQL
dynamically so it generates the string

IN ("FLA", "GA")

The other is to have a local table into which you place the selected
state abbreviations and then join to that table.

If you want the users to enter a list as a query parameter, the
requirements on the user will be quite stringent compared with the
list box. Obviously, the user would have to spell each state
abbreviation exactly and never omit a comma. In addition, you must
either have them always put a space between items, after the comma, or
never do so.

You could then use INSTR() to search the string. To do this at all
reliably, assuming the users do not put spaces after commas, the
expression could be:

InStr("," & [Enter States to filter separated by commas] & ",", "," &
StateColumn & ",") <> 0

This would search for

,FLA,

in the string of entered states

,FLA,GA,

This puts quite a burden on the user, and so is inferior to using the
list box.

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

In Access 97, I'm trying to use the IN clause as a qualifier in a
parameter
query so the user can type in more than one value to filter with on a
single
field

Hardcoding the criteria to the following works fine...
IN ("FLA", "GA")

I can't get the following to work - any advice?
IN ([ENTER STATES TO FILTER ON SEPARATED BY COMMAS])

The parameter box displays but no matter how I enter the data (quotes; no
quotes; single quotes; double quotes, etc), nothing qualifies if I enter
more
than 1 state.

 

[Guest]

Thanks for the replies, gentlemen.

Tried passing the string in the format 'IN("FLA", "GA")' to the report but
it didn't seem to work. To do so, I set the CRITERIA line for the field to
point the variable where I had the text loaded in the form. I looked at the
SQL that generated and it place an "=" sign in between the field name and the
clause which would result in the SQL expression...

GROUP = IN("FLA", "GA") which was unsuccessful.

We'll try the site you've suggested. I was hoping to be able to use the
variable approach as we are trying to link a standard report filtering screen
that is presented to the user and will generate 3 qualifiers and then have
other qualifiers within the individual query definitions.

This approach seems to lean toward a need to artificially generate the
entire where clause and pass it as an argument as opposed to the "half
artificial with the remainder through the standard query design window"
approach we were hoping for. Appreciate the help nonetheless.

Mike

If you need an example of how to code Tom's first suggestion, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Mike:

There are two approaches to make this work using a listbox with
multiple selections. One is to code the building of the SQL
dynamically so it generates the string

IN ("FLA", "GA")

The other is to have a local table into which you place the selected
state abbreviations and then join to that table.

If you want the users to enter a list as a query parameter, the
requirements on the user will be quite stringent compared with the
list box. Obviously, the user would have to spell each state
abbreviation exactly and never omit a comma. In addition, you must
either have them always put a space between items, after the comma, or
never do so.

You could then use INSTR() to search the string. To do this at all
reliably, assuming the users do not put spaces after commas, the
expression could be:

InStr("," & [Enter States to filter separated by commas] & ",", "," &
StateColumn & ",") <> 0

This would search for

,FLA,

in the string of entered states

,FLA,GA,

This puts quite a burden on the user, and so is inferior to using the
list box.

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

In Access 97, I'm trying to use the IN clause as a qualifier in a
parameter
query so the user can type in more than one value to filter with on a
single
field

Hardcoding the criteria to the following works fine...
IN ("FLA", "GA")

I can't get the following to work - any advice?
IN ([ENTER STATES TO FILTER ON SEPARATED BY COMMAS])

The parameter box displays but no matter how I enter the data (quotes; no
quotes; single quotes; double quotes, etc), nothing qualifies if I enter
more
than 1 state.

 

[Allen Browne]

That's correct, Mike. You cannot just pass the entire IN expression as a
parameter to the query. You can pass that kind of string in the
WhereCondition of the OpenReport action though. Or you can re-write the SQL
statement as you suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the replies, gentlemen.

Tried passing the string in the format 'IN("FLA", "GA")' to the report but
it didn't seem to work. To do so, I set the CRITERIA line for the field
to
point the variable where I had the text loaded in the form. I looked at
the
SQL that generated and it place an "=" sign in between the field name and
the
clause which would result in the SQL expression...

GROUP = IN("FLA", "GA") which was unsuccessful.

We'll try the site you've suggested. I was hoping to be able to use the
variable approach as we are trying to link a standard report filtering
screen
that is presented to the user and will generate 3 qualifiers and then have
other qualifiers within the individual query definitions.

This approach seems to lean toward a need to artificially generate the
entire where clause and pass it as an argument as opposed to the "half
artificial with the remainder through the standard query design window"
approach we were hoping for. Appreciate the help nonetheless.

Mike

If you need an example of how to code Tom's first suggestion, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Dear Mike:

There are two approaches to make this work using a listbox with
multiple selections. One is to code the building of the SQL
dynamically so it generates the string

IN ("FLA", "GA")

The other is to have a local table into which you place the selected
state abbreviations and then join to that table.

If you want the users to enter a list as a query parameter, the
requirements on the user will be quite stringent compared with the
list box. Obviously, the user would have to spell each state
abbreviation exactly and never omit a comma. In addition, you must
either have them always put a space between items, after the comma, or
never do so.

You could then use INSTR() to search the string. To do this at all
reliably, assuming the users do not put spaces after commas, the
expression could be:

InStr("," & [Enter States to filter separated by commas] & ",", "," &
StateColumn & ",") <> 0

This would search for

,FLA,

in the string of entered states

,FLA,GA,

This puts quite a burden on the user, and so is inferior to using the
list box.

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


On Fri, 22 Oct 2004 14:25:02 -0700, "Mike Blanchard"

In Access 97, I'm trying to use the IN clause as a qualifier in a
parameter
query so the user can type in more than one value to filter with on a
single
field

Hardcoding the criteria to the following works fine...
IN ("FLA", "GA")

I can't get the following to work - any advice?
IN ([ENTER STATES TO FILTER ON SEPARATED BY COMMAS])

The parameter box displays but no matter how I enter the data (quotes;
no
quotes; single quotes; double quotes, etc), nothing qualifies if I
enter
more
than 1 state.