Turning NULL fields to default type when you can't turn on the option to do so...

[Former Member]

Hi,

After 19 years, I'm sure that some of my coding skills are left over from my paranoid early years of learning Crystal.  And so, with that in mind, when I'm not left with the option to set NULL values to their default values, and most recently at a job that won't let me, I'm wondering if something I do is overkill or if my reasoning is actually sound.

The Situation:

You connect to tables that have NULL values, but for whatever reason (your client doesn't want to turn them on), you can't check the checkboxes in Report Options for Convert Database NULL Values to Default and Convert Other NULL Values to Default.

Reasoning:

You have seen with your own eyes that if a field IS null, when exporting to Excel, detail data shifts to the left, replacing the cells that have NULL values (and doesn't match up with headers).  This doesn't happen all the time (you've tested), but you HAVE seen it happen.  Another reason - you simply don't like dealing with NULL values or always comparing them with defaults (blank text fields, 0s, date(0000, 00, 00), etc.) in your formulas or record selection criteria.

Possible Paranoid Solution:

For the fields in question, when you can't use those checkboxes mentioned above, you write the code:

If isnull({database.field}) or trim({database.field}) = "" then "" else {database.field};

If isnull({database.field}) or trim({database.field}) = 0 then 0 else {database.field};

If isnull({database.field}) or trim({database.field}) = date(0000, 00, 00) then date(0000, 00, 00) else {database.field};

Thus, you never have NULLs to deal with and you feel you are placing "something" in each field, thus avoiding issues like exporting to MS-Excle with data shifting or performing calculations, further formulas, etc.

Is there any harm to doing this?  Or is it time to update that part of my reasoning and save my fingers a little typing?

Thank you,

Erin

[DellSC · Accepted solution]

I've also been using Crystal for about 19 years.  This is exactly what I would do - and it's what I generally tend to do instead of setting "convert NULL values to default" (that's the control freak in me - I want to know for sure that they're handled...LOL!)

-Dell

[Former Member · Accepted solution]

Hi Dell,

LOL - I can't tell you what a relief it is that this isn't some crazy idea I came up with.

Thank you SO VERY MUCH for the validation.

I'm very picky about how I set up my reports, so it's nice to know that I'm not just coding for the heck of it.

Again - my sincere thanks!  You are always so helpful.  Thanks for that.

Erin