If 'convert null to default' isn't set, what can go wrong?

[losthorizon]

When using a Microsoft Access database, is there ever any reason why we should NOT set 'convert null to default' in Crystal Reports? What are all the possible problems that could arise by NOT setting the reporting options in Crystal to 'convert null to default?' (Our Access database presently contains some null values.)

 

[synapsevampire]

If you don't set this, then you might find that checking nulls in Crystal is inconsistent with how many languages do things, especially when using a formula, and that when you attempt to concatenate or aggregate anything which has null in it provide bad results.

I check this option unless I need to check for the existence of a null value, which means that no data is in the column.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

If you don't check this setting, you take a chance that your formulas might hit a null value and they would return a null value.

However, if you DO check this setting it becomes harder to select the null values, because the SQL selection happens before the convesion. You might also find some funny behaviors, for instance you might sort a column of numbers and find some zeros before the negatives (converted nulls) and some zeros after the negatives (true zeros). Also, when you average a column, you might not want to average the nulls as zeros.

Personally, I prefer NOT to check this setting in most reports. Instead, I prefer to fix the affected formulas individually. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[rosemaryl]

Dear Losthorizon:

I am with Ken on this one (sorry SV <smile>), I never ever set this option to on!

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

I perform null checking on the database, so my approach is different than most folks anyway.

The last time I tried using isnull() in a Running Total put me over the edge, bad enough that isnull() must be the first check done in formulas (which is hoakey), it gives erroneous results on Running Totals.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear SV,

I do this mostly on the db side or in the Select Expert because it indicates for example, assignment of assets Isnull(Inventory Catalog.Configuration ID}) - No configid - no assignment for the software on which I do the majority of consulting.

I have never had a problem with the isnull check in a formula, running total, or etc....

Maybe I am just lucky or not as advanced <smile> as you!

ro

By the way...are you still in NY?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[losthorizon]

Hi! Looks like we really started something here by asking this question! Thanks all, for the quick responses. We agree mostly with SV, in our case, because it simplifies MOST of our Crystal Reports. Usually, for us, a 'null' means 'zero' (0), or a zero-length string, anyway. We aren't checking for blanks.