Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: null values Post Reply Post New Topic
Author Message
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Topic: null values
     Posted: 28 Apr 2008 at 2:09pm

The purpose of our report is to pull up any client that was served in a time range.  So we have been using the Intake "{CLIENT.InDateStart}" and the {CLIENT.InDateEnd} as agency open and close dates.  So for instance I want to know this for 1/1/07 - 12/31/07, the open date could be anything and the close could be blank or between those date.

We have worked throught the select formula, and have came to the conclusion that the end date is giving us problems.  Certain people are not showing up that should.  We looked in the SQL table to see what exactly is storing in the blank end dates, and found clients that were transferred in to casper are showing blank but new clients that I've had to enter since the transfer are storing "NULL".  Those clients that are have "NULL" in the end dates are not showing up on the report.

So the issue is how do I get blank and "NULL" values to show on my report?

This same problem is happening with a separate report where we want to view doctors appointments.  We have the comments pulling into the report after an appointment has been completed.  We want to see the items where there are no comments to prove that an appointment was missed, however, the "null" items or items where there are no comments are not showing up on the report.

HELP! 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 28 Apr 2008 at 3:24pm
One option is to click on File > Options and set the options to convert nulls to their default values. That clears up most problems. The second option is to use IsNull() in your record selection formula. When doing this, the IsNull() function must be listed before all the other conditions. Something like this should get you on the right track.
(IsNull({client.InDateStart})  OR IsNull({client.InDateStart})) OR {?Parameter1} IN {client.InDateStart} TO {client.InDateEnd}

I cover tips and tricks for handling null values and advanced record selection formulas in my Encyclopedia book. You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Posted: 09 May 2008 at 12:12pm
I have converted nulls to default values with no luck.  I have also tried a formula similar to what you have provided - changing only the fields that pertain to my report.  To date, my nulls are still not showing up.
 
The problem is this:  I am running a report to show all medical appointments that occur in a month - whether scheduled, in process or completed.  If an individual does not have an appointment, we still want their name to appear on the list - and it does not appear if there is no appointment pending.
 
Do you have any additional advice?
IP IP Logged
imstillfishing
Newbie
Newbie
Avatar

Joined: 09 May 2008
Location: United States
Online Status: Offline
Posts: 11
Quote imstillfishing Replybullet Posted: 09 May 2008 at 12:53pm

Although a field appears blank, it is not necessarily null.

Try creating a formula where you set the value to your date field.
Then use the formula field on your report vs. the date field.

Also, make sure you are not dealing with a DB Linking issue.
Depending on your data struture, and what fields you are linking by
it could appear to be a null field issue when all you need to do is
is change your join type to left outer.

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