Solved

Check String for Null value and empty string""

Posted on 2015-01-14
4
1,655 Views
Last Modified: 2015-01-15
I have a question for crystal Report.
If I want to check if field has null value or empty string “” in formula  how to do it properly?
Let say field has null value but I do not know it.

If I do check on this way:
 
if IsNull({somefield}) OR {Somefield} = ""  then
    Name:="A"
    Else
    Name:="B";
I will get value "A" if filed has null value.

But if I do this check on this way:
If {Somefield} = ""   OR  IsNull({somefield})  then
    Name:="A"
    Else
    Name:="B";
 I got not A not B as result when I put this formula in report. I got  just empty field I assume that formula returning null.

Why both ways not working and giving me same result “A” when filed has null value? I tried to use bracket but not help.

 And what if filed has some other not visible character in as Chr(10), Chr(13) Chr(9) … In first case will formula check if is not null and exit saying is not null and show me “B” and not go to check if filed has value empty string  ""??
0
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Accepted Solution

by:
vasto earned 167 total points
ID: 40550040
With Crystal you always need to check for null value before to do anything else. If the value is null and you check it for something else Crystal engine will stop the execution and any code after will be completely ignored. It is like an error, but it is not thrown.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 40550070
A little more explanation.

NULL is actually the absence of a value so it is not possible to compare it to a value.

The value Crystal returns on the termination is not defined so it could be anything.  In my experience it is generally no value but according to the write up in Crystal the value is not defined.

IN your case since there is difference between the result for NULL and "" you could use the dropdown that controls how NULLs are handled.  By default the control is EXCEPTION FOR NULLS.  If you have the dropdown in the formula editor you could set it to DEFAULT VALUE FOR NULLS then you won't have to test for NULL and can simply test for the empty string.

There is also a report level option to convert NULLs to a default value which you could set if you don't need to distinguish between a NULL value and the empty string or for numbers 0

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 40550919
And what if filed has some other not visible character in as Chr(10), Chr(13) Chr(9) … In first case will formula check if is not null and exit saying is not null and show me “B” and not go to check if filed has value empty string  ""??
The first formula would give you "B", because neither test is true.  The field contains characters, so it's not null, so the first test is false.  And characters that aren't directly visible, like a line feed, are not the same as an empty string or space.  ChrW (10) <> ""  (You can put that in a formula and see the result for yourself)  So the second test is also false, so you'd get "B".

 If you want to test for a string that is not empty/blank, but does not contain any "visible" characters, you'd need to do that a different way.

 James
0
 

Author Closing Comment

by:Taras
ID: 40551166
Thanks a lot to all of you guys.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question