cancel
Showing results for 
Search instead for 
Did you mean: 

Include null date fields in report

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Tried your formula, but now I receive no results (null or otherwise).

I am using a parameter (at the moment) to generate dates a service was initially charged.  This displays records with one field showing "date paid".  However, as I originally asked, I would also like to include anything that hasn't been paid and generate that field with a "0".

Here are my selection formulas:

{vwTranJrnlPmts.Procedure_Code} in ["AvaDD", "AvaMedic", "Avastin", "Eylea", "Jetrea", "Luc3MG", "Luc5MG", "Lucentis", "Macugen"] and

{vwTranJrnlChgs.Service_Date} = {?Service Date} and

{vwTranJrnlPmts.Date_Paid} = cdate(0,0,0) OR isnull({vwTranJrnlPmts.Date_Paid})

abhilash_kumar
Active Contributor
0 Kudos

Am I interpreting this correctly:

You'd like the report to return records where the 'Service_Date' matches the date entered by the user and also records where the 'Paid_Date' is Null (irrespective of whether or not the 'Service_Date' for such rows matches with the date selected).

If yes, then the code should look like this:

{vwTranJrnlPmts.Procedure_Code} in ["AvaDD", "AvaMedic", "Avastin", "Eylea", "Jetrea", "Luc3MG", "Luc5MG", "Lucentis", "Macugen"]

AND

(

     {vwTranJrnlChgs.Service_Date} = {?Service Date}

     OR

     (

     {vwTranJrnlPmts.Date_Paid} = cdate(0,0,0) OR isnull({vwTranJrnlPmts.Date_Paid})

     )

)

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

-Abhilash

Former Member
0 Kudos

I tried that formula, and I now get any charge with a payment, but still not getting the nulls.

Perhaps I am misinterpreting my db value as a null.

Using the accounting software that runs this db, when a payment comes in, our staff has to type a payment date or choose it from a calendar pop-up.

Would this be considered a null value (since it really is a "blank" field)?

abhilash_kumar
Active Contributor
0 Kudos

An easy way to test whether the field contains nulls or blanks or spaces etc is by creating a formula. E.g:

IF {vwTranJrnlPmts.Date_Paid} = cdate(0,0,0) then 'Null'

else if {vwTranJrnlPmts.Date_Paid} = '' then 'Blank'

else 'Something else'


'Default Values for Nulls' should be chosen from the option.


Drag and drop this formula field on the details section along with the other fields to find out what's in there.


Another way to troubleshoot this is to:

- comment the record selection formula

- create a new formula on the report and paste the code from the selection formula in here

- drag and drop the formula on the details section

The formula returns True or False for each row. Rows that return True are what eventually show up on the report and If you think there is a particular row that returns False but is supposed to be show up then you can modify the code accordingly.

-Abhilash

Former Member
0 Kudos

Tried this, but am getting an error:

Former Member
0 Kudos

Hi,

try this:

IF {vwTranJrnlPmts.Date_Paid} = cdate(0,0,0) then 'Null'

else if totext({vwTranJrnlPmts.Date_Paid}) = '' then 'Blank'

else 'Something else'



--DJ

Former Member
0 Kudos

That formula seemed to work.  No errors.

However, everything in that field populated "Something else" and there was a payment date showing up on that line, exactly the same as my original results.

No nulls or blank dates as I was hoping for.

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Why don't you run the report query directly from database by substituting the parameter values and then check what exactly is retrieved from the database for those date fields you want?

Based on the value retrieved for date fields, you can implement the required logic at report level or even directly to the database field in the query.

The reason for asking this is, you are telling you want to display 0 whenever the date_paid field value is null, but you are not sure whether it's null or something else. So, I feel, it would be best to check the values retrieved from the query used than checking at report level.

Hope this provides atleast some hint on what exactly retrieved for date_paid field.

Thanks,

Raghavendra

Former Member
0 Kudos

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.

raghavendra_hullur
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Warner,

Try this : write this formula in report selection.

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

else {field}

--DJ

Former Member
0 Kudos

I tried that formula, but it gives me an error after the equal sign, saying it expects a Boolean.