Parameter Query

[Ed S]

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

 

[learningMikey]

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.

 

[Ed S]

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?