[RESOLVED] null dates in formulas
Please help me if you can!
I have a formula to display two dates and some text if the dates are not null. But it is not working. The formula is this:
If isNull({reportview_TimeToTransfer.ExStartDate})
and
isNull({reportview_TimeToTransfer.ExEndDate}) Then
' '
Else
CStr({reportview_TimeToTransfer.ExStartDate}) & ' - '
& CStr({reportview_TimeToTransfer.ExEndDate}) & ' (Extension)'
If it worked correctly, it should show:
01/01/2002 - 12/01/2002 (Extension)
or if the dates are null, it should be blank. But if the dates are null, it is showing:
- (Extension)
Any ideas anyone?!
Re: null dates in formulas
This works fine for me:
VB Code:
If IsNull({Table1.dtStartDate}) AND IsNull({Table1.dtEndDate}) Then
''
Else
CSTR({Table1.dtStartDate}) & ' - ' & CSTR({Table1.dtEndDate})
BTW, I am using CR11
Re: null dates in formulas
I'm not sure what version of CR I'm using. It's the bundled version in Visual Studio 2005 (VS version 8.0.50727.42).
If that formula works for you, why doesn't it work for me? :mad: grrrrr I'm really starting to dislike Crystal!!!!
Do you have any particular settings that I should know about?
Re: null dates in formulas
Quote:
Originally Posted by obscuregirl
I'm not sure what version of CR I'm using. It's the bundled version in Visual Studio 2005 (VS version 8.0.50727.42).
If that formula works for you, why doesn't it work for me? :mad: grrrrr I'm really starting to dislike Crystal!!!!
Do you have any particular settings that I should know about?
Hmmm, What happens if you put the {reportview_TimeToTransfer.ExStartDate} and {reportview_TimeToTransfer.ExEndDate} next to the Formula Field, do the dates appear?
BTW, CR is one of the best (and probably one of the more expensive) reporting programs on the market (IMHO or course). Just keep at it you will come to love it for what it can do for your app!
Re: null dates in formulas
Thanks for the example. I've tried running it but can't get it to connect to the database you sent. As far as I can see it looks the same as what I have so I have no idea why it's not working!
Re: null dates in formulas
Quote:
Originally Posted by obscuregirl
Thanks for the example. I've tried running it but can't get it to connect to the database you sent. As far as I can see it looks the same as what I have so I have no idea why it's not working!
Did you try putting the two date fields next to the Formula field?
Re: null dates in formulas
Quote:
Originally Posted by Mark Gambo
Hmmm, What happens if you put the {reportview_TimeToTransfer.ExStartDate} and {reportview_TimeToTransfer.ExEndDate} next to the Formula Field, do the dates appear?
BTW, CR is one of the best (and probably one of the more expensive) reporting programs on the market (IMHO or course). Just keep at it you will come to love it for what it can do for your app!
I put it next to the formula field and it only shows if the date is not null which is what I would expect. This is why I'm so mystified about the formula seeming to ignore it!
Re: null dates in formulas
Quote:
Originally Posted by obscuregirl
I put it next to the formula field and it only shows if the date is not null which is what I would expect. This is why I'm so mystified about the formula seeming to ignore it!
Hmm :confused: :confused: :confused: , I am stumbled. Is there anyway you can zip-up your project and post it?
Re: null dates in formulas
What are the datatypes of the date fields? Based on the info they seem to be string fields containing date values rather than true datetime values.
One of the fields could contain an empty string causing its IsNull statement to return False.
1 Attachment(s)
Re: null dates in formulas
I've zipped up the report - hope I've done it right! Not sure how to send the database, don't really want to send whole thing, it's quite big! The table I'm using is called StudentExtension and the fields are:
ExtensionID (int, 4) - Primary key
ResearchStudentID (int, 4)
Startdate (smalldatetime, 4)
Enddate (smalldatetime, 4)
Thanks for your help!
Re: null dates in formulas
BTW, I've aliased the startdate and enddate fields in my view as ExStartDate and ExEndDate because there are other start and end dates in the view. Forgot to mention that earlier!
Re: null dates in formulas
Are you using a View for this report? Can you post the query.
Re: null dates in formulas
ooops! sorry! Forgot that you might need the view as well!!! Doh!
SELECT YEAR(dbo.ResearchStudent.RegistrationDate) AS RegYear, dbo.ResearchStudent.ResearchStudentID, dbo.Person.Title, dbo.Person.Surname,
dbo.Person.Forenames, dbo.Person.Initials, dbo.ResearchStudent.Mode, dbo.ResearchStudent.RegistrationDate,
dbo.ResearchStudent.DateTransferred, DATEDIFF(d, dbo.ResearchStudent.RegistrationDate, dbo.ResearchStudent.DateTransferred)
AS DaysToTransfer, dbo.ResearchStudent.Active, dbo.StudentExtension.StartDate AS ExStartDate, dbo.StudentExtension.EndDate AS ExEndDate,
dbo.StudentSuspension.StartDate AS SusStartDate, dbo.StudentSuspension.EndDate AS SusEndDate
FROM dbo.Person INNER JOIN
dbo.ResearchStudent LEFT OUTER JOIN
dbo.StudentExtension ON dbo.ResearchStudent.ResearchStudentID = dbo.StudentExtension.ResearchStudentID LEFT OUTER JOIN
dbo.StudentSuspension ON dbo.ResearchStudent.ResearchStudentID = dbo.StudentSuspension.ResearchStudentID ON
dbo.Person.PersonID = dbo.ResearchStudent.ResearchStudentID
WHERE (dbo.ResearchStudent.RegistrationDate IS NOT NULL) AND (dbo.ResearchStudent.DateTransferred IS NOT NULL)
Re: null dates in formulas
I still think the problem might be the data. Add two formulas, one for each field, to the report to check the null value.
IsNull({reportview_TimeToTransfer.ExStartDate})
Do they print True or False when you run the report?
Re: null dates in formulas
Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?
Re: null dates in formulas
Quote:
Originally Posted by brucevde
I still think the problem might be the data. Add two formulas, one for each field, to the report to check the null value.
IsNull({reportview_TimeToTransfer.ExStartDate})
Do they print True or False when you run the report?
They're false where the date is null and blank where the date has a value.
Re: null dates in formulas
Quote:
Originally Posted by brucevde
Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?
It's on.
Re: null dates in formulas
Quote:
They're false where the date is null and blank where the date has a value.
???? Using the formula that I posted that would be impossible. It would either display True or False.
Run the report with the option to "Convert Null Values.." off.
Re: null dates in formulas
Quote:
Originally Posted by brucevde
???? Using the formula that I posted that would be impossible. It would either display True or False.
Run the report with the option to "Convert Null Values.." off.
Now it's showing True where the date is null and blank where the date has a value! But the formula is showing blank as I wanted it to where the date is null! Yay!
Re: null dates in formulas
Thanks Brucevde! Once again you've solved my problem! You're a star!
Re: null dates in formulas
Quote:
Originally Posted by brucevde
Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?
Nice Catch :thumb:, I didn't even think of that :blush:. My hat is off to you sir!
Re: null dates in formulas
Quote:
Originally Posted by brucevde
Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?
Good knowledge for me also
Thanks brucevde :thumb:
