Use parameter in a filter
Hello,
I have a database field called Product.
I also have a parameter called Product. You can select individually A, B, and C.
So if you select A, B, and C in the parameter, I want the filter to capture all Products with A, B, or C in it. So for example, it would return records like:
AB
BC
AB
etc.....
Just not sure how to code that. Thanks for your help.
Joel
I have a database field called Product.
I also have a parameter called Product. You can select individually A, B, and C.
So if you select A, B, and C in the parameter, I want the filter to capture all Products with A, B, or C in it. So for example, it would return records like:
AB
BC
AB
etc.....
Just not sure how to code that. Thanks for your help.
Joel
ASKER
Thank you Ido. I actually tried that. I get an error:
"This array must be subscripted........"
"This array must be subscripted........"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually, I just used the + sign instead of & and it worked. Thank you both. I will give Ido credit since he was pretty close.
ASKER
Had to use & instead of +.
You said his doesn't work for multiple parameter values.
mlmcc
mlmcc
If the parameter allows multiple values, "*" + {?param} + "*" will not work.
CR treats multi-value parameters like an array. It's smart enough so that if you use ({field} like {?param}), it does the Like comparison on each value in the parameter (ie. each element in the array).
As you saw, "*" & {?param} & "*" gives you an error.
"*" + {?param} + "*" does not give you an error, but what it's actually doing is adding "*" (twice) as another element in the array. For example, if you entered "A" and "B", you'd end up with "*", "A", "B" and "*". So, one (or, I guess, two) of the tests ends up being ({field} like "*"), which will match everything. IOW, the report will always include all values for {field}, regardless of what you enter for the parameter.
If you need the multiple values, the simplest thing may be to include the wildcard on the parameter values. IOW, instead of "A", "B" and "C", you'd enter "*A*", "*B*" and "*C*".
Another option is to use a formula to go through each value in the parameter, add the "*" wildcards to it, and check for a match. That's what mlmcc's formula is doing.
James
CR treats multi-value parameters like an array. It's smart enough so that if you use ({field} like {?param}), it does the Like comparison on each value in the parameter (ie. each element in the array).
As you saw, "*" & {?param} & "*" gives you an error.
"*" + {?param} + "*" does not give you an error, but what it's actually doing is adding "*" (twice) as another element in the array. For example, if you entered "A" and "B", you'd end up with "*", "A", "B" and "*". So, one (or, I guess, two) of the tests ends up being ({field} like "*"), which will match everything. IOW, the report will always include all values for {field}, regardless of what you enter for the parameter.
If you need the multiple values, the simplest thing may be to include the wildcard on the parameter values. IOW, instead of "A", "B" and "C", you'd enter "*A*", "*B*" and "*C*".
Another option is to use a formula to go through each value in the parameter, add the "*" wildcards to it, and check for a match. That's what mlmcc's formula is doing.
James
For a multi-value parameter, please use and award the points to mlmcc's solution.
ASKER
Ido is correct. mlmcc's solution worked. Can anyone tell me how to re-award points to mlmcc? I can't figure it out on this newly designed site.
FWIW, if the parameter could have more than a few values, I would tweak mlmcc's formula a bit. The idea is to exit the For loop as soon as you find a match, because, once you find a match, there's no point in checking the remaining parameter values. If you only have a few parameter values, then doing a couple of extra checks probably won't make any real difference, but if you could have a lot of parameter values, skipping the unnecessary checks might save some time, especially since you're doing a Like string comparison.
James
Local NumberVar Index;
Local BooleanVar Include := False;
For Index := 1 to UBound({?My Parameter}) do
(
Include := Include OR {Product} LIKE '*' & {?My Parameter}[Index] & '*';
if Include then
exit for;
);
IncludeJames
Open in new window