Like statement with a range parameter!!!

[noxum]

I have a selection formula that is comparing a db field to a range parameter.(or trying to ;>). It looks like this:

{F0101.ABALPH} like {?Enter First Letter}

I'm using CR9, and with this formula I get the following error highlighting {?Enter First Letter}:
"A String is Required Here"
How can I get this to work for the range parameter?

Noxum

 

[pgtek]

Hi try this
is this UPUER CASE or lower case field
you might have to check and see the data to be sure
so if its UPPER
{F0101.ABALPH} like UPPERCASE({?*})
else
{F0101.ABALPH} like {?*}

cheers

pgtek

 

[pgtek]

Hi
Create a formula @Search
The following examples are applicable to both Basic and Crystal syntax:

{customer.FIRST NAME} like "D?n"

TRUE, where {customer.FIRST NAME} = Dan or Don.

{customer.FIRST NAME} like "D?n"

FALSE, where {customer.FIRST NAME} = Doug or Rob.

{customer.LAST NAME} like "*s?n*"

TRUE, where {customer.LAST NAME} = Johnson or Olson or Olsen.

{customer.LAST NAME} like "*s?n*"

FALSE, where {customer.LAST NAME} = Johnston or Smith.

Use this one
@Search
totext(left({F0101.ABALPH},1)) like "?*"
or
{F0101.ABALPH} like "?*"
or
totext({F0101.ABALPH}) like "?*"

hope this helps

pgtek

 

[lbass]

I think the problem is with using a range parameter. If you want to allow multiple letters to be selected, then change the parameter options to "discrete" and "allow multiple values" and change the formula to:

left({F0101.ABALPH},1) = {?Enter First Letter}

I don't think this will pass to the SQL statement though.

If you want the user to only choose one letter then uncheck "allow multiple values" and just use "discrete values" and change your selection formula to:

{F0101.ABALPH} like {?Enter First Letter} + "*"

This will appear in the SQL Statement. I don't know of a way of allowing the selection of multiple letters AND passing it to the SQL statement. Maybe someone else does.

-LB

 

[synapsevampire]

For optimal performance, create a SQL Expression that contains the first letter and then compare your parameter to it. Since you didn't share the database used, I can't share the exact solution, this is for SQL Server or Oracle:

substring(field,1,1)

I'm surprised that you're only allowing for a single letter though, generally you want to allow for multiples to be able to drill in better.

LB: Your last solution will work for one or more letters and pass the SQL, the only downside is that it's using the LIKE predicate, which deminishes SQL performance.

-k

 

[noxum]

Their wanting to search through a list of companies. A user would select a range of companies (ie a-g). The data resides on an AS/400 DB2. They are not looking for one particular company.

 

[synapsevampire]

Your requirements are changing, the original post had a parm of enter 1st letter.

Are you going to preload this list of companies (so as you say they can select them), or are they going to enter a range of letters, as in:

A-G

The theories expressed here are all still applicable, the difference being that you will create 2 formulas to be used in the record selection formula with wildcards by parsing the parameter choices out.

-k