Troubles with IsNull Crystal Reports

0
votes

I'm using Crystal Reports 12.3 and Peachtree Accounting 2012 to manage some database. My task now is simply make a report that contains customers that have not placed orders in last 30 days. I'm new to it so I'm asking your help.

My first trying was to use this formula (I'm doing all my stuff in Formula Editor -> Record Selection):

not ({Customers.LastInvoiceDate} in Aged0To30Days)

Everything seems to be fine except null values. I need to include in report records with null values. So I rewrite the formula to:

not ({Customers.LastInvoiceDate} in Aged0To30Days) OR IsNull({Customers.LastInvoiceDate})

But in Peachtree Accounting 2012 I'm seeing a customer with a blank LastInvoiceDate field which is not in report.

Is there some kind of bug in my last formula? Or is there some "other" black values for DateTime type?

2
crystal-reportscrystal-reports-12
If you are more comfotable with SQL than CRs i would just write your query there and then paste it into CR, then you can easily handle the nulls – Limey

2 Answers

4
votes

You should always check for nulls first, so try reversing the second formula to

isnull({Customers.LastInvoiceDate}) or not({Customers.LastInvoiceDate} in Aged0To30Days)

If Crystal encounters a null and it is not explicitly handled first, then the rest of the formula will crap out and not evaluate. Another way to get around this is to tell Crystal to use 'Default Values for Nulls' instead of 'Exceptions for Nulls' in the Formula Workshop (It's a dropdown setting in the Formula Workshop toolbar).

1
votes

length(trim({Customers.LastInvoiceDate})) = 0

One statement with no need for isnull function and OR operator.