From and To Parameters with a LIKE *

[Shav]

Crystal 11.
I am trying to put in a From and To parameter in Select expert to simplify the entry criteria for the user.

My field for From & To values follows this structure :
(examples)
60000-100-10
60000-100-20
60000-100-30
60000-200-10
60000-200-20
60101-100-10
60101-100-20
60101-200-10
60201-100-10

My report user should be able to specify either from 60000 to 60000 or 60000 to 60201 , etc. as the ranges.

I thought that I should use > LIKE {FROM}* and < LIKE {TO}*

What is the formula I should use to do this ?

 

[raja2008]

Hello Everyone!

Using Crystal reports XI R2 and i have the same question.

Didn't wanted to start a new thread since it's the same.

I have a string parameter with Multiple selection and now the user wants to be able to search like 2000* .

Please help.

Right now i have the record selection as
{table.field} like {parameter}

 

[lbass]

Try:

{table.string} in {?From}+"*" + " to " + {?To}+"*"

However, note that for {?To}+"*", the asterisk is interpreted as if it were a zero, and records are not included unless they are less than this value (as if it were an Upto, but not including).

-LB

 

[raja2008]

Hello Everyone!
Using Crystal REports XI R2.
For me the parameter is not a range.
Could be any no. of values .

If the user enters 12345 and 34567 in the parameter list,i should be able to pull all the records with these two values in them
basically it should work something like this.
{table.field} like "*"+{parameter}+"*".

With the regular multiselect parameter i use this
{table.field} like {parameter}
Hope i'm clear.

 

[lbass]

You should have started your own thread, as the solutions are different. The best solution is to decide the maximum number of values you will allow the user to select and say this in the prompt text (Select up to N values) and then write your formula like the following:

(
{table.field} like "*"+{?Parm}[1]+"*" or
(
ubound({?Parm}) >= 2 and
{table.field} like "*"+{?Parm}[2]+"*"
) or
(
ubound({?Parm}) >= 3 and
{table.field} like "*"+{?Parm}[3]+"*"
) or //etc.
)

This will pass to the SQL query. You could set this up with variables to account for any number of entries, but that would NOT pass to the SQL and your report would be slower.

-LB

 

[raja2008]

It works great.

Thank You

I even have the * as default for all the values .
So i set up the formula like this.


if {parm} <>"*" then
(
{table.field} like "*"+{?Parm}[1]+"*" or
(
ubound({?Parm}) >= 2 and
{table.field} like "*"+{?Parm}[2]+"*"
) or
(
ubound({?Parm}) >= 3 and
{table.field} like "*"+{?Parm}[3]+"*"
) or //etc.
)

else true.


Please let me know i can post everything in a new thread.