Crystal Reports XI - Isnull Formula

I am testing to see if the actual achievement date is null and if its null then I want the units column to be blank for that record rather than display 0.

Database
---------------------
Milestone No         Expected Achievement Date   Acutal Achievement Date      Units
     1                                 01/11/2006                            09/26/2006                      368
     2                                 04/25/2006                            09/26/2006                      368
     3                                 05/10/2007                                                                    0


Report
----------------------
Milestone No         Expected Achievement Date   Actual Achievement Date      Units
     1                                 01/11/2006                             09/26/2006                     368
     2                                 04/25/2006                              09/26/2006                    368
     3                                 05/10/2007                              

Are you saying that if you display a null date field from your database in your report it naturally displays as a 0?
That is not standard behaviour for CR.  You would just expect it to display a blank.

Are your date fields really date or datetime datatypes or are they stored differently??

The report is currently displaying the below information:
-------------------------------
Milestone No         Expected Achievement Date   Acutal Achievement Date      Units
     1                                 01/11/2006                            09/26/2006                      368
     2                                 04/25/2006                            09/26/2006                      368
     3                                 05/10/2007                                                                    0

I want to edit the report so it will display the following information.  The only difference between the above report and the below reports is if the actual achievement date is null then the units column will display no data for that record.
------------------------------------------------
Milestone No         Expected Achievement Date   Actual Achievement Date      Units
     1                                 01/11/2006                             09/26/2006                     368
     2                                 04/25/2006                              09/26/2006                    368
     3                                 05/10/2007              


I thought the below formula would of solved this problem but it doesn't.

If isnull({L_FUNDING.DateClaim})
then ""
else
totext({L_FUNDING.Units},0)

Are you checking the correct dat field?  The name doesn't seem to correspond with the heading.

Are the dates in the database date types or strngs?  If strings is the value really NULL or ""

mlmcc

Yes I'm checking the correct field.  The data type for the actual achievement date is set to Date/Time. I've tested it in access and the value is definitely a null

Hi Peter

Yes your correct. The option to convert database null values to defult is swichted off.

I managed to get the problem solved in microsoft access using the below formula in the report design. I had to change Units to Units1 as it must be a reserved word.

=IIf([DateClaim] Is Null,"",[Units1])

Based upon your CR problem and your Access problem, it sounds like you have an issue with your pc setup.
Your Access 'problem' should not have been a problem either.

I just found out that the option to convert database nulls was switched on in the report options menu.

I didn't think this was the problem as it was switched off in the options menu but it was switched on in the report options menu.

Fine- at least the cause was diagnosed in the end.
Thanks.