Null/Blank Values in database and displaying strings

[grendel81]

Hi, I am trying to conditionally display a string if the field is blank or null. We have a database in which the users enter in their comments. If there is a comment, then the data is shown. If there are no comments, they currently have to type in "No comment". What I would like to have is the report to automatically display "No Comment" if the field is left blank.

Currently the table is auto populated so that there is a record created for each commentable field. The blank comment is always numbered 0.

I have tried isnull(comment.field) and trim(comment.field)="", neither of which have displayed the string. I have also tried if bullet.num = 0 then "No Comment" - that doesn't work either.

Any ideas?

I am using CR9 and DB2

 

[lbass]

Well, if users are currently supposed to be entering "No Comment", you might not have nulls or blanks. You might try:

if isnull({table.comment}) or
trim({table.comment}) = "" or
{table.comment} startswith "No" then
"No Comment" else
{table.comment}

-LB

 

[grendel81]

Well, the field is blank or null in the table at the moment. This particular string shouldn't display if there is anything in the field.

what I have tried so far:

if (isnull({table.comment}) or trim({table.comment}) = "") and table.bullet_num = 0 then
"No comment"
else
{table.comment}

I have tried taking out each of the booleans, and even removed them all, which should make the field display "No Comment" no matter what. Nothing displays unless there is some value entered into the field.

 

[lbass]

Did you try:

if table.bullet_num = 0 then
"No comment"
else
{table.comment}

What display do you get if you place {table.bullet_num} and {table.comment} in the details section? From what you have said it should look like:

bullet# comment
0
0
1 good candidate
2 no experience
0
3 interview
0

-LB

 

[grendel81]

Thanks for your assistance on this.

What I get currently is
Bullet_num table.comment
0
1 good candidate
2 a different comment

What I want to have is:
0 No Comment

Unless there are comments, in which case bullet 0 is suppressed.

1 good candidate

In order to display the headings, the table is keyed with a bullet 0 under each headings. That way the users can run their section of the report, and see what it will look like in printed form. If we don't have the bullet 0 keys in the database, the headings don't show up, and the users don't see the blanks that they need to fill in.

What I don't understand is why I can't use isnull and "" to look for a blank spot in the text field. When I look in the database, there is nothing in the field - wouldn't the coding above take into account all the possibles for the field being blank? Thie is the real problem I am having, that the text field doesn't display if the field is null in the database, even though the display string formula uses isnull.

I should also clarify that the text will change based on the heading. In some places it'll be "No Comment", under other headings it'll be "No Issues", and in other places it'll be some other default text.

 

[lbass]

Again, did you try:

if table.bullet_num = 0 then
"No comment"
else
{table.comment}

Although this isn't relevant to the above formula, you also might check file->options and file->report options and see if "Convert nulls to default values" is checked. If it is checked, then the null check won't work.

-LB

 

[christinetjx]

Hi, i was wondering if you ever came across a resolution to this problem. I am having the same issue(I use Crystal 8.5).

chris b

 

[towntopic]

try this for your formula

if isnull({table.comment}) or {table.comment} = "" then "No comment"
else
{table.comment}


that should fix your problem...null is no data, "" is for a space filled field...you have to test for both.

 

[grendel81]

We ended up adding a third field to the table, default_text. I then added a running total counting the number of records displayed. If RT0 = 0 then table.default_text else table.commment.