Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Test for a null value in a date field Post Reply Post New Topic
Author Message
brent21090
Newbie
Newbie


Joined: 20 Apr 2009
Online Status: Offline
Posts: 31
Quote brent21090 Replybullet Topic: Test for a null value in a date field
    Posted: 13 Nov 2009 at 7:54am

Hi,

I am trying to return all records that may have even a null value in a date field. In crystal how would I test that so that even if the date is null return the record and assign a 0 in that field.
 
I am having a problem where I am expecting some records to be returned but because some of the records have a null date value nothing is being returned.  So, I am assuming that crystal will return any records if they have null values. Is this true?
 
Thank you
LC
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Nov 2009 at 8:14am
Crystal can return NULL values but it depends onyour source data, your select statement and possibly joins.
you can create a formula:
if isnull(datefield) then 0 else 1
 
or I would do it as a select statement as it is easier to see:
isnull(datefield)
IP IP Logged
brent21090
Newbie
Newbie


Joined: 20 Apr 2009
Online Status: Offline
Posts: 31
Quote brent21090 Replybullet Posted: 13 Nov 2009 at 8:45am

IF ISNULL({SH.SH_DATE})
THEN ""
ELSE  ({SH.SH_DATE})

I am using the above formula and its giving me an error that a string is required? And I think it has to do with "" because when I say return 0 it says a number is required.  The data type of the field I am using is date field with time etc.
Can you see anything wrong with this formula?
 
Thank you
PS All the links and joins are correct
IP IP Logged
brent21090
Newbie
Newbie


Joined: 20 Apr 2009
Online Status: Offline
Posts: 31
Quote brent21090 Replybullet Posted: 13 Nov 2009 at 8:47am
IF ISNULL({SH.SH_DATE})
THEN ""
ELSE  ({SH.SH_DATE}
The above msg returns a string required error but if I change "" to 0 it returns a number error.  What is wrong with my formula?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Nov 2009 at 9:31am
You cannot mix data types in your display values.
Your first options returns an empty string ("") and your second option returns a date (SH_DATE) so it chokes on mixing the two.
Same issue for the 0, numneric mixed with date.
For testing purposes just go with something very clear like
IF ISNULL({SH.SH_DATE})
THEN "I AM A NULL"
ELSE  totext({SH.SH_DATE})
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.