Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Pulling "Null" Records Post Reply Post New Topic
Author Message
hackera40
Newbie
Newbie
Avatar

Joined: 22 Mar 2011
Online Status: Offline
Posts: 9
Quote hackera40 Replybullet Topic: Pulling "Null" Records
     Posted: 05 Apr 2011 at 3:07am
I am trying to pull records that have 2 criteria that I need.  One is numeric and the other is NULL.  Here is an example of what it looks like and the result I am looking for.
 
Field1    Field2    Field3     Field4    Field5   Field6
 
AAAA     Blahh    Blahh      Blahh        3       Blahh
BBBB     Blahh    Blahh      Blahh                 Blahh
AAAA     Blahh    Blahh      Blahh        6       Blahh
AAAA     Blahh    Blahh      Blahh        1       Blahh
AAAA     Blahh    Blahh      Blahh                 Blahh
BBBB     Blahh    Blahh      Blahh        3       Blahh
BBBB     Blahh    Blahh      Blahh                 Blahh
 
The records that I want in my report are all records with Field1 being AAAA and Field5 being 3 AND Null.  So the report should look like:
 
Field1    Field2    Field3     Field4    Field5   Field6
 
AAAA     Blahh    Blahh      Blahh        3       Blahh
AAAA     Blahh    Blahh      Blahh                 Blahh
 
So far I can get the Field1 AAAA and the Field5 3 but I can't get both Field5 3 AND Field5 Null.
 
My selection criteria is {DUMMY.FIELD1} = "AAAA" and {DUMMY.FIELD5} in [3] or isNull({DUMMY.FIELD5})
 
Can anyone help me with this issue?  Very frustrating.
 
Thanks!
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 05 Apr 2011 at 3:20am

You have to check for null first.  The results of a comparison with null are ALWAYS null - not true or false.  In fact, the expression null = null evaluates to null, not true.  Also, you're missing some parentheses to group the Field5 criteria together.  So, your selection criteria should be:

{DUMMY.FIELD1} = "AAAA and
(IsNull({DUMMY.FIELD5}) or {DUMMY.FIELD5} = 3)
 
For more information about null handling, I posted a note titled something like "What is Null and Why is it Important" to the SAP Crystal Reports and Dashboard Development Facebook page last fall.  Facebook is blocked here at work, so I can't get to the URL for it right now but it shouldn't be too difficult to find.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
hackera40
Newbie
Newbie
Avatar

Joined: 22 Mar 2011
Online Status: Offline
Posts: 9
Quote hackera40 Replybullet Posted: 05 Apr 2011 at 3:44am
It works!!  Big%20smile
 
First of all, I want to thank you for the quick reply and secondly for giving me the answer to my dilemma I was looking so hard for.
 
I too cannot access Facebook from work, (and I'm the Network Administrator), but be assured that I will look for your post once I get home.  I am always interested in learning more when I don't know the answers.
 
Thanks again,
Al
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.