Print Page | Close Window

Test for a null value in a date field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8326
Printed Date: 05 May 2024 at 5:39am


Topic: Test for a null value in a date field
Posted By: brent21090
Subject: Test for a null value in a date field
Date 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


Replies:
Posted By: DBlank
Date 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)

Posted By: brent21090
Date 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

Posted By: brent21090
Date 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?

Posted By: DBlank
Date 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})


Print Page | Close Window