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
Genius123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Use a filter condition following this logic:
{Product} like "*" & {?Product} & "*"

Open in new window

0
Genius123Author Commented:
Thank you Ido.  I actually tried that.  I get an error:

"This array must be subscripted........"
0
mlmccCommented:
I thought that wouldn't work so I tried it myself and got the same result.

Try it this way

Local NumberVar Index;
Local BooleanVar Include := False;

For Index := 1 to UBound({?My Parameter}) do
    Include := Include OR     {Product} LIKE '*' & {?Product}[Index] & '*';
Include

Open in new window


mlmcc
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Genius123Author Commented:
Actually, I just used the + sign instead of & and it worked.  Thank you both.  I will give Ido credit since he was pretty close.
0
Genius123Author Commented:
Had to use & instead of +.
0
mlmccCommented:
You said his doesn't work for multiple parameter values.

mlmcc
0
James0628Commented:
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
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
For a multi-value parameter, please use and award the points to mlmcc's solution.
0
Genius123Author Commented:
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.
0
James0628Commented:
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.

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;
);
Include

Open in new window


 James
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.