Edit Parameter Field Crystal reports with NOT value?

Question

In Crystal Reports, I want to add a WHERE field <> date to filter out dates that have a NULL value from my database in my report.

I'm using a legacy FoxPro database on the backend which generates an SQL statement from my report, but doesn't appear to have anyway of adding a WHERE clause to the generated statement.

When accessing the FoxPro backend directly, dates with psudo-NULL values have a date of 1899-12-30, but when they are pulled from FoxPro through Crystal they appear as 12/30/99 (which is maybe the same date just displayed in MM/DD/YY format).

I noticed that the report had an existing Parameter Field that prompts the user to filter out the original query down to a specific date range. I tried to add my own in addition to the Parameter Field, but discovered that what I needed with my WHERE field <> date is not an available option since there are only 3 types of Field Parameters mainly:

Discrete

• Accept single and discrete values.

Ranged

• Accept a lower and upper value in order to select everything in this range.

Discrete and Ranged

• A combination of the two above

None of these appear able to filter the results of the query using a WHERE NOT type of clause, is there some other way to do this?

Answer 1

All I really needed to do was add some criteria to the WHERE clause of the SQL statement, simple enough in an SQL client, but when you're doing this in Crystal Reports v10 it's a bit difficult to find, unless you know what you are looking for...

So what I needed to do was:

1. Select the field to filter by in the report (in the Details section)
2. Click the Select Expert button on the Experts toolbar.
3. In the Select Expert dialog the name of your field should appear in a tab.
4. Below you can select the WHERE criteria used to filter the records.

Answer 2

Add this to your record-selection formula:

// remove actual nulls
AND Not(Isnull({table.date_field})) 
// remove old dates 
AND {table.field} <> date(1899,12,30) 
// remove dates not in select parameter value
AND {table.field} IN {@date_parameter}