Solved

Check String for Null value and empty string""

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

23 Experts available now in Live!

Get 1:1 Help Now