Text Issues

[MikeCitizens]

Hello,

I have a SQL7 Database of Customer info. Every customer is assigned a shortname. There are two types of customers. My users a long time ago made a decision to add a *O to all of one type of customer. Now they would like a full list of all these customers with the *O in their shortname. Is there a crsyatl formula that can include only shortnames that have these certain characters? The *O is never in the same place either usually follows directly after the shortname. Shortnames can vary in length.

Please help

Thanks in advance

Mike

 

[bdreed35]

You could use the Instr function in your to determine if the string "*O" exists in your shortname field. You could place this in your record selection criteria:

Instr({table.field}, "*O&quot > 0

The problem would be that the Instr function is a Crystal function that won't get passed in the SQL statement. I would create a SQL Expression. In the SQL Expression use the Locate function like this and call it:

{fn LOCATE('*O',table.`field`)}

In your record selection criteria, add a reference to your new SQL Expression and then add " > 0" after it. You should have something like this:

{%my_SQL_Expression} > 0

This will get passed to your database.

~Brian

 

[Ngolem]

Why make us GUESS??? show us some examples of these short names

Jim Broadbent

 

[MikeCitizens]

Thanks your suggestion worked.

I used Instr({table.field}, "*O&quot > 0
it pulled all the shortnames correctly.

Appreciate your help

Mike