Using Memo Field in Formula

[joie]

I am using Crystal Reports Professional 8.5. I have a Customer Address (4 "lines&quot which is being stored in my database as a single field with semicolons indicating the separation of the four lines. This {arCustomer.Addr} field is being read by Crystal 8.5 as a memo field. I need to use formulas to parse the Address out to 4 lines on my report. But, because it is a memo field, I get an error message saying I can't use Blobs or Memo fields in formulas. Does anyone know a way around this? I've tried typing in the field name and typing in {arCustomer.Addr field} to no avail. Please help!

 

[synapsevampire]

The quick cheat is to use a SQL Expression to parse the data into another column on the server side, as in:

replace({table.field}, ';', char(13))

This would return a memo field containing the 4 fields with a carriage return in place of the semicolon.

-k

 

[joie]

I am getting an error "Column not found/specified" when I try to add this to my report. Could you be more specific? Thanks!

 

[synapsevampire]

You have to use your table and column name, the above is just an example, they'll be in the list.

If you receive errors, please post what you tried.

-k

 

[joie]

Sorry... Still no luck.

My table is the arCustomer table. The field is the "Addr" field. I am selecting all Customers with an open balance. I'm designing a form letter that will show the name, address, etc. in the header and standard text in the body. When I put the address field on the report, I discovered the 4 lines are strung together in the database and read:

NAME OF CONTACT;STREET ADDRESS;ADDRESS2;ADDRESS3

My initial SQL Query was:

SELECT
arCustomer1."CustNum", arCustomer1."Name", arCustomer1."Addr", arCustomer1."City", arCustomer1."St", arCustomer1."Zip", arCustomer1."CntryCode"
FROM
"PUB"."arCustomer" arCustomer1
WHERE
arCustomer1."OpenBalance" > 0
ORDER BY
arCustomer1."CustNum" ASC

This generates the information I want. To work with the memo field arCustomer1."Addr", I tried inserting 'AND replace (arCustomer1."Addr",";",char(13))' into the Where section of the query. I, alternately, tried inserting 'arCustomer1."Addr"= replace (arCustomer1."Addr",";",char(13))'

Either way, I end up with the error above. I'm editing this in Crystal Reports 8.5>Database>Show SQL Query. Perhaps there is a different place I should be trying this, but everywhere else (Selection formula or other formulas), I get a warning that I can't use Memo fields in a formula.

Thanks for any help.
-J

 

[synapsevampire]

I suggested a SQL Expression, not a change to a where clause in the SQL.

Use Insert->Field Object->Right Click SQL Expression fields and select New

-k