Problem with formula returned values 1

[clevelandjwb]

I have a Basic syntax formula of the form:

formula=iif(a<>"G",b,iif(c="I",d,e))

a is a database field which is also a group control it can have two values "G" or "O" (letter)
b is a literal of type string (ie in quotes)
c is a database field or type string
d is a formula which should evaluate to a string
e is a database field of type string

The formula for "d" could produce very long strings IE up to 300 characters although those in the test data are no longer than 150 character.

variable "a" controls the innermost control which has the purpose of inserting a blank line between the two groupings when both are present.

When the report is displayed, it will only display properly the detail field when a is = "G", this is the group that comes first. When the group that has a="O" prints this field shows as blank. If I change the above formula around and make the first iif test a="G" then the literal displays for all of the first group but the second group is still blank. I have tried this formula in both basic syntax and crystal syntax with the same result.

I am using crystal 8.5.

I found something in the help about the need to have the formula always set to the same data type. I don't know if it might be changing the result of the string formula to a memo type which was the original source of the problem.

It might be helpful to state the original problem here because if it were solved the above might not matter other than as a learning experience.

The field in question was originally calculated in an SQL formula for which SQL declared the type of the field as MEMO. When printed it is listed as "can grow". The problem was that the memo field seems to be padded with spaces making all of the fields have several lines of blanks after them. I tried to create a formula to RTRIM the database field but crystal will not allow the MEMO type fields to appear in formula.

I am a novice at Crystal but have a fair amount of programming experience in other areas. If you have read this far, you are probably very dedicated to helping others.

John

 

[synapsevampire]

Use a SQL Expression to trim the field. Insert->Field Object->Right click sql expressions:

rtrim(table.field)

-k

 

[synapsevampire]

I should mention that you won't be able to select it from the list because it's a memo field, just manually type it.

-k

 

[clevelandjwb]

Thanks.

After reading about an hour's worth of other problems (once I figured out how to use this system) I had come a similar conclusion. My table is being created by a stored procedure which I modified by adding left(xxx,250) to force SQL to create a string rather than a memo. I first checked and found that the longest one was 93 characters so I think I have left plenty of room and if anything gets truncated it will not be significant.

John