Filtering substring out of text field

[ctwilliams]

There is a text field in my SQL database that contains comments that begin with &quot;[!<&quot; and end with &quot;>!]&quot;. I want to filter these comments out so that they do not display on my report. Is there a Crystal function I can use to filter these? I've heard that you cannot use a text field in a formula.

Example of text field value:

blah, blah, blah, blah, blah, blah
[!< This is a comment >!]
blah, blah, blah, blah, blah, blah

 

[dgillz]

Would the entire line be a comment or just the stuff in between &quot;[!<&quot; and &quot;>!]&quot; ? Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[ctwilliams]

Just the stuff in between &quot;[!<&quot; and &quot;>!]&quot;

 

[dgillz]

One last question - Is the list of text one field like a memo field or is it several records in one database? Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[ctwilliams]

It is one big text (memo) field in the database. The comments are sometimes split onto two or more lines within the field, for example...

blah, blah, blah, blah, blah, blah
blah, blah, blah, blah, blah, blah

[!< This is
a comment >!]

blah, blah, blah, blah, blah, blah

 

[dgillz]

If your field is a memo field you cannot use it in any formulas.

If the entire line of data is a comment, you can exclude these from the report by using the following as your record selection formula: left({fieldname},3)<>&quot;[!<&quot;

To go through a string and take out anything in between the &quot;[!<&quot; and &quot;>!]&quot;, use the following formula:

Replace({fieldname},Mid({Fieldname},InStr({Fieldname},&quot;[!<&quot,InStrRev({fieldname},&quot;>!]&quot-InStr({Fieldname},&quot;[!<&quot,&quot;&quot

The replace fucntion is fairly straighforward. The syntax is Replace(field,StringToBeReplaced,StringToUseInstead)

InStr(fieldname,string) returns the first occurence of your subtring, InStrRev() returns the first occurence of your ending substring, but it starts from the far right of the string instead of the far left. The Mid() function uses the InStr and InStrRev functions to grab a substring of everything in between the begining and ending characters you are using.

Finally, the replace function replaces that string with a null string &quot;&quot;.

This will not work if there is more than one &quot;comment&quot; in a single database record. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[ctwilliams]

Thanks! However, my field IS a memo field so I can't use any of these formulas. Is there a way to convert it to a varchar field or split it into substrings? I'm selecting this field out of the database using a view.

 

[dgillz]

Maybe you can does this with a SQL or MS Access query, then write your crystal report against the query. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com