[RESOLVED] [2005] Exclude nulls in Crystal Record Selection Formula

[obscuregirl]

Hi

Can anyone tell me how I exclude any records with a null value in a certain column when building a Record Selection Formula?

Thanks.

[eSPiYa]

I'm not sure with this but still try:

[Field] <> Null

or

NOT [Field] = Null

[obscuregirl]

I tried "and not (IsNull ({reportView_CompletedAll.Decision}))", this comes up with no errors but doesn't bring back the results I'm expecting!

[eSPiYa]

This work fine:
not isnull({[table].[field]})

I see that there is an "AND" in your statement, what is the other criteria?

[obscuregirl]

Year ({reportView_CompletedAll.DateAwarded}) in {?Academic Year} to ({?Academic Year} + 1)
and
if Year ({reportView_CompletedAll.DateAwarded}) = {?Academic Year}
and Month ({reportView_CompletedAll.DateAwarded}) > 8 then True else False
or
if Year ({reportView_CompletedAll.DateAwarded}) <> {?Academic Year}
and Month ({reportView_CompletedAll.DateAwarded}) < 9 then True else False
and not(IsNull ({reportView_CompletedAll.Decision}))

[brucevde]

It looks like you are selecting records for an entire year (from Aug, 1 to Aug 31)

{reportView_CompletedAll.DateAwarded} in DateSerial({?Academic Year}, 8, 1) to DateSerial({?Academic Year} + 1, 9 ,1) And
Not IsNull ({reportView_CompletedAll.Decision})

Crystal seems to have problems generating the SQL statement properly when using IsNull. Anyways, try the above and if it still does not work post the Where clause of the SQL Statement that is generated by Crystal (Database -> Show SQL Query menu).

[obscuregirl]

It's still bringing back records that have a null value in the {reportView_CompletedAll.Decision} field.

Here's the SQL it generates:
SELECT "reportView_CompletedAll"."DateAwarded", "reportView_CompletedAll"."fore", "reportView_CompletedAll"."Sur", "reportView_CompletedAll"."Mode", "reportView_CompletedAll"."RegistrationDate", "reportView_CompletedAll"."Name", "reportView_CompletedAll"."Decision", "reportView_CompletedAll"."CompDate", "reportView_CompletedAll"."ECName"
FROM "ResearchDB"."dbo"."reportView_CompletedAll" "reportView_CompletedAll"
ORDER BY "reportView_CompletedAll"."Name", "reportView_CompletedAll"."Mode"

[brucevde]

In Report Options, are the following Checked or UnChecked.

Convert Null Field Value to Default
Use Indexes or Server for Speed

[obscuregirl]

The "Convert Database NULL Values to Default" and "Convert Other NULL Values to Default" options were not checked but the "Use Indexes or Server for Speed" was checked.

I've tried checking and unchecking them in various combinations but it doesn't make any difference.

[eSPiYa]

I think the problem is your Formula/Criteria.
I have no idea what you want to do (I'm not good in reading long post/code coz I don't read it at all ).

I'll give you a tip.
Try query it w/ other criteria, put the criteria one-by-one.
As long as it returns records it is ok, debug it.

[brucevde]

The Formula I posted is fine, at least it works in Crystal 8.5 against the Northwind.Orders table (SQL Server).

Does the date portion of the formula work for you?

What I find curious is that there is no Where clause being generated by Crystal.

[obscuregirl]

Yeah, I thought that was weird too!

The dateserial formula works fine, it's just the part where I want to exclude any records that have NULL in the {reportView_CompletedAll.Decision} field. Even with the Not IsNull ({reportView_CompletedAll.Decision}) line (and variations thereof!) it still brings back these rows!

[brucevde]

Perhaps the Decision field isn't equal Null but "". To check add the following formula and place it on the report.

If IsNull({reportView_CompletedAll.Decision}) Then
   "Null"
Else
   If {reportView_CompletedAll.Decision} = "" Then
        "Empty String"
   Else
        {reportView_CompletedAll.Decision}

[obscuregirl]

I put a slightly altered version of your code in (it brought up errors without the alteration) but doesn't give any different results, it's like it's not even looking at that field.... the altered version is below:

If IsNull({reportView_CompletedAll.Decision}) Then
{reportView_CompletedAll.Decision} = "Null"
Else
If {reportView_CompletedAll.Decision} = "" Then
{reportView_CompletedAll.Decision} = "Empty String"
Else
{reportView_CompletedAll.Decision} = {reportView_CompletedAll.Decision}

[brucevde]

No. I meant create a new Formula. The code I posted does not belong in the Record Selection Formula.

Print/show this new formula somewhere on the report, preferrably the details section and let me know what gets printed for a row that is null.

[obscuregirl]

Ah! I see what you mean! Sorry!

I created a formula called nullField and put in on the report, the fields that i thought were null were in fact empty strings as you suggested. Then I added the following code to my record selection formula but it made no difference, can you see what I'm doing wrong? Thanks for all your help!

select {@nullField}
case "Null": False
case "EmptyString": False
default: True

[brucevde]

The new formula was just to help in debugging the problem. There is no reason to use it in the Record Selection formula.

Since the Decision field is an empty string the following should work

{reportView_CompletedAll.DateAwarded} in DateSerial({?Academic Year}, 8, 1) to DateSerial({?Academic Year} + 1, 9 ,1) And
{reportView_CompletedAll.Decision} <> ""

[obscuregirl]

Thanks, I've just tried that now but it makes no difference whatsoever! I looked at the SQL being generated by Crystal and there is still no where clause being included! I'm baffled!

This is my first time using Crystal, I had no idea it would be so difficult and uninituitive! Give me a programming language anyday!!! Is that your experience of it too?

[obscuregirl]

OK, I surrender! I've changed the way I get the data. I'm now using a view which contains a where clause to exclude the null, I can't see any other way to do it! Thanks so much for your help Brucevde!