Solved

Check String for Null value and empty string""

Posted on 2015-01-14
4
1,337 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
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
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 …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now