Extract Ltd or Limited from a string field

[slickp]

Hi, I'm running a report in Crystal to try and find all the limited companies we have in our client table.

So obviously I need to run a formula to find any of the client names that end with either "Ltd" or "Limited".

Any help?

 

[slickp]

I've tried the formula below:

If Right ({CLIENT.ClientName}, 3) = "Ltd" then "Yes" else
If Right ({CLIENT.ClientName}, 3) = "ltd" then "Yes" else
if Right ({CLIENT.ClientName}, 7) = "limited" then "Yes" else
if Right ({CLIENT.ClientName}, 7) = "Limited" then "Yes" else "No"

And then in the selection expert just picked all the ones equal to "Yes".

It seems to have brought back some results. Does anyone think this is corrct

 

[Madawc]

I'd simplify it to a boolian, thus:

Right ({CLIENT.ClientName}, 3) = "Ltd" or
Right ({CLIENT.ClientName}, 3) = "ltd" or
Right ({CLIENT.ClientName}, 7) = "limited" or
Right ({CLIENT.ClientName}, 7) = "Limited"

Display all of the records with this beside it, returning True or False. Check it against the actual data. If it's OK, put the boolian in the selection - it selects only those where the answer is True

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[slickp]

The formula is fine but I can see just one problem. Say if there is a client name with Ltd in the name but has a different ending i.e. something in brackets.

Is there a function I could use to search a string for a particular string of characters.

I've looked at the Filter function but it keeps on saying that my client name field is not a string array.

Any help

 

[lbass]

Try:

{CLIENT.ClientName} like ["*Ltd*","*ltd*","*Limited*","*limited*"]

You also might want to create a SQL expression like {%LTD}:

ucase({CLIENT.ClientName})

Then change the record selection to:

{%LTD} like ["*LTD*","*LIMITED*"]

-LB