Parameter Query

[Guest]

I am using the In operator in a query to match against a list of values. I'd
like to specify the list as a parameter, e.g. In ([enter values]). I have
tried to specify a coma separated string of values when prompted for the
parameter but instead of the parameter being interpreted as a list by the In
operator, it appears to intepret it as a single value and attempts to match
on the whole thing, commas and all. I don't want to list the values one at a
time as paramater1, parameter2, etc. as I would like to have a variable
number of values. Make sense? Any suggestions?

 

[Tom Ellison]

Dear Ed:

I never like having any user (including myself) free type a value that is
already stored in the database. It is better to give the user a list of
values from which to select. This eliminates all opportunities to
mysteriously "mis-spell" or mis-punctuate or mis-anything the exact value as
already entered in the database. It therefore gives good, clean results.
If a value is spelled/punctuated two different ways in the database, then
both will be in the list, allowing the user every opportunity to get it
right.

For your needs, I would strongly consider a multi-select list box. Then
generate the SQL from this in code.

The way you suggest doing it:

- requires users to type in every alternative, with commas, exactly as
needed
- cannot handle any values that contain a comma
- isn't supported by any existing features of Access (you'd have to program
all of it yourself)

Actually, the non-support for your concept is a good thing. I strongly
suggest it shouldn't be done that way, but using some feature like what I've
suggested (even though Access support is also lacking in this case).
Indeed, experienced database programmers are fairly unanimous on this.

Tom Ellison

 

[Guest]

Try Variable IN ("Value1","Value2","Value3") for e.g. Country IN ("USA",
"Canada","Brazil"). If the variable you are comparing against is numeric you
don't need quotes.

 

[Guest]

Thanks! I agree with your observations. Looks like more work, but the right
way to go.

Dear Ed:

I never like having any user (including myself) free type a value that is
already stored in the database. It is better to give the user a list of
values from which to select. This eliminates all opportunities to
mysteriously "mis-spell" or mis-punctuate or mis-anything the exact value as
already entered in the database. It therefore gives good, clean results.
If a value is spelled/punctuated two different ways in the database, then
both will be in the list, allowing the user every opportunity to get it
right.

For your needs, I would strongly consider a multi-select list box. Then
generate the SQL from this in code.

The way you suggest doing it:

- requires users to type in every alternative, with commas, exactly as
needed
- cannot handle any values that contain a comma
- isn't supported by any existing features of Access (you'd have to program
all of it yourself)

Actually, the non-support for your concept is a good thing. I strongly
suggest it shouldn't be done that way, but using some feature like what I've
suggested (even though Access support is also lacking in this case).
Indeed, experienced database programmers are fairly unanimous on this.

Tom Ellison

I am using the In operator in a query to match against a list of values.
I'd
like to specify the list as a parameter, e.g. In ([enter values]). I have
tried to specify a coma separated string of values when prompted for the
parameter but instead of the parameter being interpreted as a list by the
In
operator, it appears to intepret it as a single value and attempts to
match
on the whole thing, commas and all. I don't want to list the values one
at a
time as paramater1, parameter2, etc. as I would like to have a variable
number of values. Make sense? Any suggestions?

 

[Michel Walsh]

Hi,


Another solution can be to use the operator LIKE, but in a not habitual way:


WHERE ("," & parameter & ",") LIKE "*[, ]" & fieldName & "[, ]*"


(syntax for numerical list of parameters).


Which is similar to an IN list, but accepts a variable number of values, in
the list.


As example, if you wish to get records where the mentioned fieldName has a
value in (1, 2, 5, 7), but note that the number of arguments can be
variable, here, there are 4, but it work also if there are 3 of 5 values in
the list. The parameter would be "1, 2, 5, 7". Le where clause become,
after the concatenation occurred with that string:


WHERE ",1, 2, 5, 7," LIKE "*[, ]" & fieldName & "[, ]*"


So, for a record where fieldName = 2, that further evaluates to:

WHERE ",1, 2, 5, 7," LIKE "*[, ]2[, ]*"


which is true


Alternatively, if a record has the value 122 in the field name, then

WHERE ",1, 2, 5, 7," LIKE "*[, ]122[, ]*"


evaluates to false and the record is not kept, which conclude the similar
behavior to a variable IN -list.


Note that it would be faster to push the values as primary key in a
(temporary) table, and to use an inner join, mainly if the other table has
also an index on the field which is implied in the test and its number of
records is large.


Hoping it may help,
Vanderghast, Access MVP