need to get substring of memo type in cr8.0 1

[ylar15]

Hi

I saw several solutions for this question but all for crystal report 8.5. Well, I am using cr8.0 and there I didn't saw any substring function. I need to get first 255 characters from memo type field.

Does anybody have a solution?

 

[FVTrainer]

The only method I'm aware of is to use a stored procedure or view as the report datasource and use the substring function within the sp or view to convert the memo field, as in the following example:


select name, substring(note,1,250) as notes
from client_notes

In the example above, the field "note", which is a memo field, is interpreted by Crystal as a string field. If your database does not support stored proc's or views...I'm not sure of how to go about doing it.

 

[vidru]

What about a SQL Expresion field?
Insert>SQL SQL Expresion field>Click the 'New' button.
Give it a name, then in the sql expression editor, type your formula like this:

substring(notes, 1, 255)

-dave

 

[synapsevampire]

If you need to use the text in a formula, you can create as many SQL Expression fields as required, providing they are 254 chars or below.

Depending upon your database, the above syntax may not work, for instance Oracle uses Substr, not Substring.

But if you select the function from within the SQL Expression builder, you'll see that it produces something like:

(fn substring(table.field,1,254)

Which should be passed using the correct SQL for your database.

Now you can use these SQL Expression formulas within other formulas, and concatenate them into a single field later by dropping them into a text field object.

-k

 

[FVTrainer]

Thanks for the additional info/correction. Before my original response, I tried using the SQL Expression builder to do just what both dave and -k suggested. I tested against a SQL Server db using both the ODBC driver and p2ssql.dll. However, the memo field didn't (and still isn't) showing in the field tree of the SQL Expression builder. I am using CR 8.5 instead of 8, but is that really the issue? Is there another reason for not seeing the memo field in the SQL Expression Builder?

 

[vidru]

Crystal won't show a memo field in the Report Fields list inside a formula. That's just the way it is. You canhowever, still reference it in a SQL Expression formula.

-dave

 

[FVTrainer]

Thanks for the clarification. It would not have occured to me to just type the field name in.