Memo fields and formulas

[jharrin]

I have a client that is using a memo field (>254 char.) on a report. The memo has text and dollar amounts they want to omit. Of course, memo fields are not available to formulas in Crystal 8.5 so I devised a way around it. I created a SQL view that broke the memo field into multiple text fields of 254 characters. I then could apply a formula to each field to remove characters that were in between left and right square brackets. Then "piece" the memo field back together again by using multiple fields in a text box. My conundrum is this, if a left bracket is located at the end of a text field and the matching right bracket is located at the beginning of the next text field, the text is not omitted in the second field. Just like a word processors "widow/orphan" syndrome. Here is the formula I use on each of the 45 fields using basic syntax:

Dim StringInput as string
Dim LeftBracket as string
Dim RightBracket as string

StringInput = {xvr_sNotes.Text1}
LeftBracket = chr(91)
RightBracket = chr(93)

Dim StringLength as number
Dim Increment as number
Dim Output as string
Dim Include as number

StringLength = Length(StringInput)
Include = 1

Do until Increment = StringLength
Increment = Increment + 1
If StringInput(Increment) = LeftBracket then Include = 0
If StringInput(Increment) = RightBracket then Include = 1
If Include = 1 and StringInput(Increment) <> RightBracket then Output = Output + StringInput(Increment) Else Output = Output + " "
Loop

Formula = Output

Any suggestions or comments greatly appreciated.
--James Harrington

 

[vidru]

You could use InStr to find out if the first bracket found in the string is a left or right bracket. If it's a right bracket, then remove the left part of the string up to the bracket. Then, you can use the InStrRev function to check the last bracket from the end, etc. After you've got the 'meat' of the string, send it through your loop.

-dave

 

[jharrin]

Thanks Dave! I'll give it a try. Info is greatly appreciated.
-- James