blob field or memo field can't be used in formula

[sharugan]

I have a field with more then 250 size characters.When i add the field as Data base field , i can add and see the values.But i want to apply formula on the field to match the value of the field with other table field.
When i do so , i am getting "blob field or memo field can't be used in formula".

can any one suggest me a way to convert the memo field into text /string so i can compare with other field.
i tried with if (mid ({table.field),1,125} = table2.field)
but i am still getting the error

 

[lbass]

What version of CR? Try using a SQL expression like:

substr(table.`field`,1,125)

Or:

{fn substring(table.`field`,1,125)}

You will have to type in the table and field name. Use the punctuation appropriate to your datasource.

-LB

 

[sharugan]

i am using CR 8.5

I followed as you said . but i am getting syntax error when creating SQL expression .FYI i still don't see the field in the SQL expression editor to browse because of 255 chars

SUBSTR({table.field},1,125)

 

[lbass]

You can't enter it with curly brackets, but to see how the punctuation should be used, go to database->show SQL query and observe how the punctuation is used there. What database are you reporting on?

Also, as I said, the field won't be in the fieldlist--you have to type it into the SQL expression editor. This should work with a memo field.

-LB

 

[sharugan]

thanks for the suggestion.I am using Oracl 9i.
When i create SQL expression with

substr("tablen"."field",1,2) , I am not getting any error
but i am not getting any values though i refresh the reports.The show sql query is also disabled.As you said that field is not available.Though i type the field, i am not getting any values.But when i drag as data base field , i am getting values.
Could you please guide me resolve the issue

 

[dgillz]

How are you connecting? What type of database? You should be using an ODBC connection, not a native connection.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

What is the actual table.field name?

Exactly how did you use the SQL expression in the report? Originally you wanted to set it equal another field, and you showed that you wanted to use the first 125 characters of the memo field to match the second field. Your last example of the substring shows only two characters--which is it? How many characters are found in {table2.field} (the one you want to match)?

-LB