formula not working

[Dukester0122]

Here's the formula:
CASE {RM20101.RMDTYPAL}
when 3 then {RM20101.TRXDSCRN}
when 7 then {RM20101.TRXDSCRN}
when 9 then {RM20101.TRXDSCRN}
else
isnull(subsctring({CN00300.TXTFIELD},1,250),")
end

I get this error:
The remaining text does not appear to be part of the formula.

 

[lbass]

What is this formula supposed to accomplish? Where are you using this? The first part is the same as:

if {RM20101.RMDTYPAL} in [3,7,9] then
{RM20101.TRXDSCRN}

...but it looks like you are then trying to say that if it isn't one of those numbers then some other field should be null--but you can't force a field to be null, you can only check whether it is. You could have the formula return a null, but not return a null field. So again, what are you trying to do?

-LB

 

[Tatertot45]

It is hard to say what the problem is. I typically get that error when a quotation or paren is missing. Is your field a string field or number field? Also, what section is not "part of the formula"? To me, it looks like you could ahve a few different issues with this formula, but we need more info.

 

[Dukester0122]

the formula is supposed to look at RMDTYPAL(smallint) whose value ranges from 1-9 but if the value is 3,7 & 9 it should show TRXDSCRN. If not show CN00300.TXTFIELD(text field) whose keyable length is 3200 but I only want to see 250.

you can suggest another formula than CASE.

 

[lbass]

if {RM20101.RMDTYPAL} in [3,7,9] then
{RM20101.TRXDSCRN} else
left({CN00300.TXTFIELD},250)

What version of CR are you using? The above should work, unless you are using a version earlier than 9.0 and the textfield is a memo field--in which case there is a different solution.

-LB

 

[dgillz]

Please note that the 'then' and 'else' values need to be of the same data type. So if this is not the case you may need to convert one or both of them to text to satisfy this requirement.

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

 

[Dukester0122]

I'm getting "blob field or memo field cannot be used in a formula". I'm still using CR 8.5.

 

[lbass]

You should always specify your CR version up front. Go to the field explorer->SQL expression and enter one of the following (whichever works):

substr(CN00300.`TXTFIELD}`,1,250)

or try:

{fn substring(CN00300.`TXTFIELD}`,1,250)}

The punctuation depends upon your datasource/connectivity. Check database->show SQL query to see how you should use the punctuation here. Let's say you named the expression "textA". Then change your formula to:

if {RM20101.RMDTYPAL} in [3,7,9] then
{RM20101.TRXDSCRN} else
{%textA}

-LB

 

[Dukester0122]

that's so weird. i can't find CN00300.TXTFIELD in SQL Expression Editor. the table is there and other field except for this txtfield.

 

[Dukester0122]

i still tried this:
substring(CN00300.TXTFIELD,1,254)

then formula:
if {RM20101.RMDTYPAL} in [3,7,9] then
{RM20101.TRXDSCRN} else
{%textfield}

result:
ODBC error. column prefix 'CN00300' does not match....OK...then Error detected by database DLL

 

[lbass]

Yes, you have to type in the memo field name--it won't be in the field list. Are you saying you were able to save the SQL expression? Do you have the table CN00300 added to your report?

-LB

 

[Dukester0122]

was able to save it without error and yes the table is in the report.

 

[dgillz]

Try this:

CN00300."TXTFIELD"

In your SQL expression. Note the double quotes around the field name.

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]

Please post your SQL query (from database->show SQL query).

-LB