Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

sharugan

Programmer
Jul 23, 2007
14
US
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
 
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
 
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)
 
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
 
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
 
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

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top