NULL values in Crystal Reports

Question

I am trying to produce a Crystal Report showing MAIN and ALTERNATE contact details for our customers.

All works fine if the customer has both types of addresses (i.e. MAIN and ALTERNATE) but no report comes out at all for customers who only have a MAIN address. So, to summarise, all customers will have a MAIN address but only some will MAIN and ALTERNATE addresses.

I need to get the report to print sheets for all customers where or not the second address (in this case in our database as Addresses2.AddressType is ALTERNATE or NULL).

My selection formula is shown below:

{SubscriptionMembers.Current} and
{Categories.CategoryType} = "M" and
{SubscriptionMembers.MainSubscription} and
{Addresses.AddressType} in ["MAIN"] and
{Addresses2.AddressType} in ["ALTERNATE"] and
{Addresses.Country} = "United Kingdom" and
not {SubscriptionMembers.Lapsed} and
not {Contacts.Deleted}

Answer 1

I would recommend to use SQL Expression Field, containing something like (isnull({Addresses2.AddressType}, 'ALTERNATE')) and use this field in record selection formula - this way you do not depend on report options about handling NULL values etc.

Answer 2

Because only some have an 'Alternative address', the selection formula is only going to show customers with a 'main' AND 'alternative'.

I assume every customer has a main address A way I would deal with this is take out {Addresses2.AddressType} in ["ALTERNATE"] and and just drop the 'alternative' address details in the same section(group) as the main address.

As long as these are linked correctly it will display every customer from your other selection parameters, therefore will show 'main' and the customers who have an 'alternative' address. How it should look:

Group - CustomerID

'MAIN' address details here               'ALTERNATE' address details here

Group - Details

'DATA' here

Answer 3

Since you need To get customer in either case then one way would be dont take address in record selection formula. Instead create a formula as to check null for address2

If isnull ({Addresses2.AddressType} in ["ALTERNATE"]) then your code Else If (Addresses.AddressType} in ["MAIN"] or {Addresses2.AddressType} in ["ALTERNATE"]) Then your calculation

This way you can handle easily