Not in the period returns blank report

[Former Member]

I am new to Crystal - be kind.

Using XI

Database is on SQL 2000

Creating a report of items checked out and not returned. The return date in the data base is <Null> if item is out. I tried using a formula: IsNull() but get a blank report when I know there are at least 180 items out.

When I go to SQL and run a query it works fine.

I also tried using "is not in the periord - AllDatesToToday" and get a blank report.

Lil' help.

[Former Member · Accepted solution]

Try testing for blanks as well:

IsNull() or = ""

Are you putting in the record selection formula and is there anything else in there?

[Former Member · Accepted solution]

I tried both of your suggestions, but neither worked.

For Jame's suggestion I still get a blank report.

For Brian's I get an error "a date time is required here"

I am doing this through the Select Expert - if that was your question Brian.

[Former Member · Accepted solution]

If you right-click and choose Browse Data on your date field what is the Field Type?

Is it actually a date field or is it a string?

[Former Member · Accepted solution]

DateTime

[Former Member · Accepted solution]

So if you remove your record selection (select expert) you DO get records...

I'd like for you to go to Report | Selection Formulas | Record

and use this formula:

(IsNull()) OR (Date () < date(1900,01,01))

Let us know how it works out...

[Former Member · Accepted solution]

Bingo! Thank you so much.

Like I said - I'm just learning.

I just started reading CRCP Crystal Reports Certified Professional All-in-One Exam Guide, and I just got thrown into this responsibility. Don't get me wrong. I want to be as good as you guys some day.

Thanks again

[Former Member · Accepted solution]

You may also want to go to File | Report Options and mark the upper left two boxes:

Convert Database NULL Values to Default

Convert Other NULL Values to Default

The re-run the report.

[Former Member · Accepted solution]

I am using a Template that someone else set up and those boxes are checked.

Thanks again.

[Former Member]

A null date is a tricky thing so try this.

Use this for your record selection:

{Return Date Field} < date (1900,01,01)

(Or use any past date that was prior to the first return date in your database....)