Problem with Formula Field When No Data Is Returned (1 Viewer)

[lagyossarian]

I have a formula field in a report:

If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_AgentAlertData.LevelOne} = true then Chr(254) else Chr(168)

It works fine unless the dataset the report template binds to is empty or null. Then it gives me an error saying it expects a string. So, I tried to trap for a null field like this:

If (Not IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsReport_AgentAlertData.LevelOne})) Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_AgentAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

However, that did not work either -- probably because of the no data again.

I am at a loss here how to handle this. Any suggestions/guidance/help is much appreciated. Thanks in advance.

 

[boblarson]

It's been a while since I worked with Crystal Reports, but maybe this will work:

If [B][COLOR=red]Len([/COLOR][/B]{Nec_Gnav_ReportViewer_Data_AgentAlertsReport_AgentAlertData.LevelOne} [B][COLOR=red]& "") > 0[/COLOR][/B] Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_AgentAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

 

[kevlray]

The formula might have worked if you used parans for the Not function
If (Not (IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}))) Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

But depending on the data you might have to check for a blank also
If (Not (IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}))) or Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne} = "" Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

 

[boblarson]

The formula might have worked if you used parans for the Not function
If (Not (IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}))) Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

But depending on the data you might have to check for a blank also
If (Not (IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}))) or Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne} = "" Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

kevlray - Just thought you might like to know that the LEN code I used (checking the length of the field with the empty string added) will take care of both nulls and empty strings. If the field is null the addition of the empty string keeps an error from happening and if an empty string is there adding another one doesn't do anything and the length is still 0. I thought you might like the explanation as it can shorten your code considerably when you need to check for both.

 

[kevlray]

kevlray - Just thought you might like to know that the LEN code I used (checking the length of the field with the empty string added) will take care of both nulls and empty strings. If the field is null the addition of the empty string keeps an error from happening and if an empty string is there adding another one doesn't do anything and the length is still 0. I thought you might like the explanation as it can shorten your code considerably when you need to check for both.

I had never tried that before, I will keep that one around since we have to check for nulls and empty strings a lot.

 

[luo_jhui]

I have exactly the same problem today, and luckily I got the answer. The problem here is pretty awkward. I believe it is a bug of Crystal itself. But we can work around it. The field {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} is boolean when the report has data, and it is a string when the report has no data. So we have to somehow be able to handle these 2 data types in one statement. the answer is: CDBL can accept both boolean and string and convert it to a number, then we can further convert it back to boolean by CBOOL, so the solution to your question can be:
If cbool(cdbl({Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne})) = true then Chr(254) else Chr(168)