Skip to Content
avatar image
Former Member

Include null date fields in report

How can I include null date fields in my Crystal Report?

I am trying to find all charges that have not been paid over 30, 60 and 90 days.

One of my report fields is "date paid", and I would like this to show "0" and be included in my report, not skipped.

I would like to compare this date to my "date billed" field, so I can determine how many days have gone by with no payment.

I have changed the General Settings to "Convert Database NULL values to default" and "Covert other NULL Values to default", but still see none of these records.

Appreciate any help.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Sep 19, 2014 at 07:49 AM

    Hi,

    Do you have a record selection formula and if yes, what does it look like at the moment?

    Here's an example Record Selection Formula that should bring in dates fields with Null values:

    {database_date_field} = cdate(0,0,0) OR isnull({database_field_field})

    Make sure 'Default Values for Nulls' is selected from the drop-down on the top in the Formula Editor.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Raghavendra Hullur

      This report is trickier for me to figure out at this point. I may have to put it aside for now and perhaps come up with an alternative.

      Thanks for all the suggestions.

  • avatar image
    Former Member
    Sep 18, 2014 at 10:37 PM

    Hi Warner,

    Try this : write this formula in report selection.

    if isnull({field}) = '' then 0

    else {field}

    --DJ

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 19, 2014 at 05:08 AM

    Hi,

    If the records with date paid = null are not retrieved, then there is something you have to check with join conditions or filters in the query you have used (assuming you have written a command object query to retrieve data).

    Or, if the records are retrieved with date paid as null and you want to set some default value (say 0) for null value, you can use Dhananjay's suggestion and display that formula instead of your database field for "date paid".

    Thanks,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 19, 2014 at 01:46 PM

    Hi Warner,

    Is that "Date paid" filed available in the database and if it contain null values, what is the option you selected in the crystal report "Convert Database NULL values to default" and "Covert other NULL Values to default", will try to display that filed value with default values.

    For more understanding can you attach the report with saved data?

    --Naga.

    Add comment
    10|10000 characters needed characters exceeded