Printing characters to right or left

[Hacim]

Using CR 8.5 w/SQL 2000
I am trying to create a Crystal formula that would print all characters to the right (or after) the words "Work Performed:" and then an other formula that would print just the characters to the left (or before) the words "Work Perfomred:" to go on another part of the report page.

Unfortunately, we have to input different types of information into the same {wo.comments}field

The second problem is that this {wo.comment} field is in a subreport, so I think I need to create two shared variables and use them in the main report. I think I can handle the variable part. Any help thanks.

 

[vidru]

This will work only if 'Work Performed:' appears in every single record and only once:

//Formula @LeftSide:
stringVar array x := (Split ({wo.comments},'Work Performed:')) ;
x[1];

//Formula @RightSide:
stringVar array x := (Split ({wo.comments},'Work Performed:')) ;
ltrim(x[2]);

-dave

 

[Hacim]

Thanks for your help.
I ran into another problem "Blob field or memo field cannot be used in a formula."
I wondered why the {woc.texts}field was not visible in the formula editor when I tried to create the variables.
Thanks for your response.

 

[vidru]

Had a feeling that your comments field was either an ntext or memo field. You'll have to convert it with an SQL Expression field before you can use it.

Make a new SQL Expression field with a formula like this:
convert(varchar(255),wo."comments&quot

Then replace {wo.comments} with the name of your SQL Expression.

-dave

 

[synapsevampire]

Better still, create the 2 formulas you need using SQL Expressions.

Here's an example of getting the left side:

substring({wo.comments}, 1,charindex('Work Performed:',{wo.comments})-1)

The right:

substring({wo.comments}, charindex('Work Performed:',{wo.comments})+15,1000)

That way if there's more than 254 chars, you don't have to lose data, and it will be much faster as the server does the work.

-k

 

[Hacim]

I typed into the SQL Epressions Editor:
convert(varchar(255),woc."texts&quot and inserted it into the report in place of field {woc.texts} and is still blank in the report.

I created SQL expression:
substring({woc.texts},1,charindex('Work Performed:',{woc.texts})-1) and when I tried to insert it and got an error
"[ODBC SQL Server Driver]Syntax error or access violation"

Just curious, how can you insert a field in the SQL Expression editor if it doesn't show up in the list of available fields?

Maybe I should also add that this report was designed to run as a work order report. I call it a template. When the report is printed (or run) through the software, it will only show me the records for one distinct work order.

Appreciate your help so far.

 

[vidru]

I get the same error with SV's, but the SQL is definitely valid.

Have you considered using a stored procedure instead?

The reason you can use your ntext fields in a SQL Expression even though they don't show up in the list is that Crystal sends whatever you type directly to your server. In other words, it writes a select statement, using your expression as the column name. At least I think that's what it's doing...

-dave

 

[WKiess]

Hi,
I've had a similar problem. I am trying to show a memo field (using CR8.5 and ODBC connection into a D3 Database) but need to convert the VMs into CRLFs or spaces. (The maximum length of the field is currently 1195chars but is expected to grow further.) However, I cannot get the SQL Expression method to work. I get a "syntax error" and the cursor is positioned in front of the field name which is FSLOGOUT.Comments. Does anyone have any other suggestions?
Thanks in advance.