Empty and isNull

[TheGoldMineGuru]

Crystal XI:

My select expert forumla is as follow:

{?CustomerID} = {table.CustomerID} and
{?LocationID} = {table.LocationdID}

This works fine if both the CustomerID and LocationID parameters have values (1234 & xyz). However, if the LocationID is not specified (brought over as an empty value), which is a valid combination, than the report does not return any records.

What should my select expert look like? I have tried multiple combinations but have yet to find the right one.

Thank you!!

 

[TheGoldMineGuru]

Please ignore. I simply set the option to 'Set Database Null to default'. Things work fine now.

 

[Madawc]

Removing nulls works, but it also destroys a level of meaning in the data. Null should mean 'we don't know' rather than 'we definitely know this value is blank / zero'.

You could keep the nulls and do your selection using formual fields, a couple of boolians: e.g.

isnull ({table.CustomerID})
or
{?CustomerID} = {table.CustomerID}

and similar for location. Then in the selection, have

@GoodCustomer and @GoodLocation

It's also best to set select using Report > Selection Formula > Record. The 'expert' functions are fine when you are new to Crystal, but also limited.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10