Check String for Null value and empty string""

Posted on 2015-01-14
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
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
 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  ""??
Question by:Taras
LVL 18

Accepted Solution

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.
LVL 100

Assisted Solution

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

LVL 34

Assisted Solution

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.


Author Closing Comment

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

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
across tab - Distinct Count - Crystal Reports 2 81
Crystal - variable evaluate after with criteria 4 53
Conditional Text in Crystal 4 51
Mask on Parameter CR2008 28 52
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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