Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Nov 12, 2015 at 07:25 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.