[RESOLVED] null dates in formulas

[obscuregirl]

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?!

[Mark Gambo]

This works fine for me:

If IsNull({Table1.dtStartDate}) AND IsNull({Table1.dtEndDate}) Then
    '' 
Else
    CSTR({Table1.dtStartDate}) & ' - ' & CSTR({Table1.dtEndDate})

BTW, I am using CR11

[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? grrrrr I'm really starting to dislike Crystal!!!!

Do you have any particular settings that I should know about?

[Mark Gambo]

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? 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!

[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!

[Mark Gambo]

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?

[obscuregirl]

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!

[Mark Gambo]

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 , I am stumbled. Is there anyway you can zip-up your project and post it?

[brucevde]

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.

[obscuregirl]

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!

[obscuregirl]

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!

[brucevde]

Are you using a View for this report? Can you post the query.

[obscuregirl]

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)

[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?

[brucevde]

Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?

[obscuregirl]

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.

[obscuregirl]

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.

[brucevde]

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.

[obscuregirl]

???? 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!

[obscuregirl]

Thanks Brucevde! Once again you've solved my problem! You're a star!

[Mark Gambo]

Just thought of something else. There is an Report Option that "Converts Null Values to Database Defaults". Is it On or Off?

Nice Catch , I didn't even think of that . My hat is off to you sir!

[shakti5385]

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