Need help with a select formula for null & blank dates.

[stormtrooper]

I want my report to select only null or blank dates from a date field. I have this and it does not work. What am I missing?

isnull ({turned_on_dt}) or {turned_on_dt} = " "

Thanks in advance.

 

[rosemaryl]

Dear StormTrooper,

That should work. What version of Crystal, what db, and in File/Options/Reporting do you have Convert Null Field Value to Defaults checked or unchecked?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[stormtrooper]

I'm using CR7 off of a ODBC DB2 database. The turned_on_dt field is a date field. I get a message saying "A date is required here."

I just tried checking and unchecking the Convert Null Field and I get the same thing.

 

[dgillz]

The message indicates that the field is not a date field.

Are you sure the field is a data field? Some programs, Macola and ACCPAC among them, store their dates as an 8 digit integer in a YYYYMMDD format. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[rosemaryl]

Dear StormTrooper:

The problem is the:

or {turned_on_dt} = " "

portion of your script, since it is a date field it is not valid for it to be = to a string!

Take the or portion off and see what happens,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[stormtrooper]

Ok, if I take off the or portion of my formula, it works fine. But now how do I account for selecting the blanks?

 

[Naith]

You have a few options open to you.

One of them is to scrap the isnull and/or = ' ' and in your selection formula use the formula:

not ({turned_on_dt} in DateTime (0, 0, 0, 00, 00, 00) to DateTime (9999, 12, 31, 00, 00, 00))

Here I've catered for turned_on_dt being DateTime. If it isn't, just lose the double zeroes, and switch DateTime to Date.

This should do you.

Naith

 

[stormtrooper]

Naith, I end up with no data at all using your formula. Thanks for the suggestion though. Still looking...

 

[Naith]

Well, is there some reason why you can't totext your date so that you can apply the ' ' rule to it?

isnull({turned_on_dt}) or totext({turned_on_dt}) like ' %'

I put in a like clause in case you have dates with a string of spaces. Though, if {turned_on_dt} is a true date field, I'm at a loss as to how it contains spaces in the first place.

 

[stormtrooper]

Thanks Naith. I think that solved it.