[b]Select Date Range AND Blank Date Field[/b]

[IbeKaba]

I'm working with Crystal Report 9, and here is what I have in the Record Selection Formular window:

{contact_calls.solicitor} = {?Select_Solicitor} and
{contact_calls.drive_code} = {?Select_Drive} and
{contact_calls.regarding} = {?Select_Regarding} and
({contact_calls.planned_call_date} >= {?Cur_Date} or
isnull({contact_calls.planned_call_date}))

As you can see, I'm trying to pull records that fit the first three criteria. However, for the fourth criteria, I want all records that fit within a certain date range AND those record that fit the first three criteria but have nothing entered in the "planned_call_date" field. Basically, if it fits the first three criteria, and the "planned_call_date" is null, I want to see it.

The above formular is returning no errors, however, when I run the report, it's not pulling those records that have no date entered in "planned_call_date" field. And I know there are some in the database.

Am I going about this all wrong, or is something wrong with my formular? Please help.

Thanks,
ibe

 

[synapsevampire]

Try:

(
isnull({contact_calls.planned_call_date})
and
{contact_calls.solicitor} = {?Select_Solicitor}
and
{contact_calls.drive_code} = {?Select_Drive}
and
{contact_calls.regarding} = {?Select_Regarding}
)
OR
(
{contact_calls.planned_call_date} >= {?Cur_Date}
)

This states that you want all rows which have a null date field and the 3 criteria, OR you want those that are within the date range.

Your description is a run on sentence that could be interpreted different ways.

I would state the criteria in a list, such as:

I want all records that fit within a certain date range

I also want all records that fit the first three parameter criteria but have nothing entered in the "planned_call_date" field.

As I said, I could be misunderstanding the inten, and it's usually best to share the database type, example data and the expected output.

-k

-k

 

[lbass]

I think you're looking for:

(
isnull({contact_calls.planned_call_date}) or
{contact_calls.planned_call_date} >= {?Cur_Date}
) and
{contact_calls.solicitor} = {?Select_Solicitor} and
{contact_calls.drive_code} = {?Select_Drive} and
{contact_calls.regarding} = {?Select_Regarding}

-LB

 

[IbeKaba]

Thanks all. Ibass, you were right on point. It work perfectly!
Maybe for a little education, can you tell me why mine didn't work? Because I think it's basically the same, except you switched the argument upside down.
Thanks a bunch
ibe

 

[lbass]

You always have to check for null fields first, before checking for any non-null values in the field. Otherwise, a request to evaluate for certain values is interpreted to mean that only non-null values are under consideration.

-LB

 

[IbeKaba]

Got it.
Thank you very much
ibe