How to show parameter with 'LIKE' pattern 3

[lana123]

I continue to develop my big report and I cannot find out how to do one for the first look simple thing:
I want to create the parameter with 'LIKE' pattern:
ex:{table.name}like {?param1}
It's gives me in query value for field without '%':name like 'value'
I was trying to do write it with the different ways:
ex:{table.name}like {?param1}+'*'
{table.name}like {?param1}'*'
{table.name}like '{?param1}+*'
Nothing help!
Any ideas?
I'll appreciate any answer
Lana.

 

[johnvai]

Hope this helps:

select * from Table where name Like '%" & Text1.text & "%'"

If you are using Msaccess then you should replace "%" by "*"

Hope helps!

 

[lbass]

If you want to use the "like" operator, you could create a select statement like the following:

{table.name} like {?param1}

And then in the set default parameters screen, add an edit mask like the following:

AAA\*

where the A's represent the number of characters to be entered and the \* results in an automatic * to be added to the end of the three character entry.

Since you seem to be requesting only the characters that start the field, you could use "startswith" instead, without the need for an edit mask:

{table.name} startswith {?param1}

-LB

 

[lana123]

Thanks everybody for responding to me!
Unfortunately, it's not exactly what I wanted to create.
I don't need the mask for LIKE because the number of the letters which users insert should be random.
I have to add this "*" automatically but I still get an error when I'm inserting this expression:
{table.name}like '{?param1}& '*'(error:This array must be subscripted); because I have parameter not the table field;

If I use: {table.name}like '{?param1}+ '*',
I get the wrong results( in query:Like 'abc'or like '%').
Anybody else had the identical problem?
Thanks in advance.
Lana.

 

[johnvai]

Dear Lana:

try "...{table.name} like '" & {?param1} & "'% ..."

 

[lana123]

Thanks,but...
This is my code:
if {?SelectAgency }='AgencyName' then
{ENTITY.ORGANIZATION_NAME} like '" & {?AgencyValue} & "'%"
else if {?SelectAgency }<>'AgencyName'
then false
THE ERROR is:
The mathching &quot; for this string is missing.
Thank you for help.
Lana

 

[synapsevampire]

If this is going into the record selection formula from within Crystal, use:

(
if {?SelectAgency }=&quot;AgencyName&quot; then
{ENTITY.ORGANIZATION_NAME} like {?AgencyValue} + &quot;*&quot;
else if {?SelectAgency }<>&quot;AgencyName&quot; then
true
)

-k

 

[lbass]

Since it appears you are only expecting users to enter the first letters of the name, why not use &quot;startswith&quot; which doesn't require the use of the asterisk? It allows variation in the number of characters entered and also passes to the SQL.

-LB

 

[synapsevampire]

LB: I avoid using Crystal functions in mosty record selection formulas because once they get complex, these functions miraculously stop passing the SQL, whereas the hard coded values do not.

I've demonstrated this odd behavior (at least in CR 8.5) in many posts here, and I believe that my FAQ here also touches on this.

When you only have a few criteria, you're fine, but once you get multiples going, often times you have to manually override, hence I don't bother with it.

-k

 

[Turkbear]

Hi, To augment what synapsevampire posted try this after designing your report:
Look at what shows in the Show Sql window..
If the selection criteria you specified are not listed after the where statement then all the data in the source will be returned to Crystal and the criteria applied then...This is true for any of your criteria that do not show in that window, even if some do appear.

Not checking this on big, complex reports can really cause performance ( and even time - out) problems.

( Been there, done that....)

 

[lana123]

Thanks everybody for help!
I found the solution to insert this into formula:

(if {?selectAgency }='AgencyName' then
{ENTITY.ORGANIZATION_NAME} startsWith({?AgencyValue})
else if {?selectAgency }<>'AgencyName'
then false)

Yes,it stop passing SQL but it gives the right selection for the report.
One more thing.TOur Oracle database is Case Sencitive and users don't have to think about it when they insert the information.I decided to do it this way:
{ENTITY.ORGANIZATION_NAME} startsWith(ProperCase({?AgencyValue}))
I got the error:This array has be subscripted.
When I use the regular field,everything works fine.But I have to work with parameter.
Does anybody know how to do it with parameter?
I'll appreciate any answer.

P.C. Thanks for all of you,people!During this short time I knew a lot about the Crystal!
Lana.

 

[lbass]

Not sure this will work with your particular database, but you could try:

(if {?selectAgency }='AgencyName' then
uppercase({ENTITY.ORGANIZATION_NAME}) startsWith({?AgencyValue})
else if {?selectAgency }<>'AgencyName'
then false)

where you have added an edit mask to {?AgencyValue} like:

>CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

where the number of C's = the length of your longest string. This would force any parameter entry, whether in Proper, lower, or upper to appear in uppercase and therefore to match the database field when converted to uppercase.

-LB