on 09-18-2014 11:30 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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})
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
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)?
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Warner,
Try this : write this formula in report selection.
if isnull({field}) = '' then 0
else {field}
--DJ
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.