Select Expert use "Like" formula with multiple values parameter

I have one parameter have to setup for use to enter multiple values. In the Select Expert, I am using "Like" formula below and keep getting the error message.

{Command.Code} like "*" & {?CodeSet} & "*"

How can I make this like formula works with parameter with multiple values?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

What is the error message?

You can try using this as the formula (assumes that is the only criteria

Local NumberVar Index;
Local BooleanVar bResult := False;

For Index := 1 to UBound({?CodeSet}) do
    bResult := bResult OR ({Command.Code} like "*" & {?CodeSet}[Index] & "*";

Open in new window


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
The error is because CR treats a multi-value parameter like an array, so you're trying to concatenate a string and an array, and CR won't do that.  It's the same as trying something like "*" & [ "1", "2", "3" ] & "*" (assuming that ?CodeSet is a string parameter).

 From a test that I just did, you can use Like with a multi-value parameter (or array) that includes values with wildcards.  For example, ( {Command.Code} like [ "*a*", "*b*" ] ) should include any records where Code includes "a" or "b".  But you have to include the wildcards on each value in the parameter, not just add them to the beginning and end of the list of values in the parameter, which is what you were trying to do.

 If ?CodeSet is a string parameter, then the simplest answer would be to have the user enter the wildcards.  For example, they enter "*a*", instead of just "a" (without the quotes).

 Another option would be to have a formula copy the values from the parameter to an array variable (because you can't change the values in a parameter), add the "*"s to each value in the array, and then use ( {Command.Code} like your_array_variable ).

 Or you can add the "*"s to each value and compare them individually, as mlmcc suggested.

 I don't know if either of the last two options has any real advantage over the other as far as performance goes.  Either way, you're looping through the parameter values and adding the "*"s to each value.

rowfeiAuthor Commented:
Thanks, all.

mlmcc- I do have another criteria in the formula (works fine since this parameter is not multi-value). Please see it below.

{Command.Code1} Like "*" & {?CodeSet1} & "*"

How to combine it with your codes below together?

Local NumberVar Index;
Local BooleanVar bResult := False;

For Index := 1 to UBound({?CodeSet}) do
    bResult := bResult OR ({Command.Code} like "*" & {?CodeSet}[Index] & "*";

Thanks again!!!
Not sure you can directly.

Try this

{Command.Code1} Like "*" & {?CodeSet1} & "*"

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.