Need To Check For Nulls And Force A Date

[Former Member]

I'm trying to check to see if a field is blank (null or blank), if so, force a date into it. My formula is giving me zeros. What is wrong with the below formula ?

If {Sales_Primary.Type_Of_Sale} = 1 And

Isnull() Then 1999-01-01

[Former Member]

Someone tried the code inside of another question and provided an answer.

[Former Member]

I'm close to resolving my issue of inserting a date into my report, but not quite there. After unchecking the "Convert Database NULL Values to Default" box in Report Options, my

report was decreased by 40 records (using both the "Default Values For Nulls" and "Exceptions For Nulls") in the Formula Editor. But I was able to insert my desired date of 9/9/1999. Now, I just have re-gain the 40 records. Below is the code used. Thanks ! ! !

If {Sales_Primary.Type_Of_Sale} = 1 And

Isnull({TableName.Sales_Date}) Then #9/9/1999#

Else

{TableName.Sales_Date};

[Former Member]

I think I have it. I have to do more testing. I turned off 'Convert Database NULL Values to Values to Default' in 'Report Options'. I don't under why, but for now I'll except the results. I hope by turning off Convert Database NULL Values to Values to Default', it will not cause any problems with other records in the database. Any advice ? Thanks to all ! ! !

If {Sales_Primary.Type_Of_Sale} = 1 And

Isnull(TableName.Sales_Date}) Then #9/9/1999#

Else

{TableName.Sales_Date};

[Former Member]

I've tried each solution mentioned so far, but with no success. I try to narrow down the problem by separating the if statements. For some reason, I'm not given data at all for the below statement. If I get this right, I should be well on my way. "{TableName.Sales_Date}" is a Date-Time field if it makes any difference.

If Isnull({TableName.Sales_Date}) Then #1/1/1999#

[Former Member]

Two things:

1) in the formula designer, ensure that you have "Defalt values for nulls" selected in the top right of the formula editor.

2) the default value assigned to a null datetimes by crystal when 'default values for nulls' is selected is (NullDate, NullTime). You should check for the null condition by using:

isnull(<your field>) 

[Former Member]

Jason/Raghavendra,

If {Sales_Primary.Type_Of_Sale} = 1 And

Isnull({TableName.Sales_Date}) Then #1/1/1999#

(No data)

If {Sales_Primary.Type_Of_Sale} = 1 And

(Isnull({TableName.Sales_Date}) OR {TableName.Sales_Date} = "") Then #1/1/1999#

(Error ===> Data-time is required for ===> = "")

If {Sales_Primary.Type_Of_Sale} = 1 And

trim()="" Then Date("1/1/1999") (Error ===> String is required for

[Former Member]

My bad. I don't think Date Time fields have "empty strings" but rather 0's instead.

Try replacing the ="" with =0

If {Sales_Primary.Type_Of_Sale} = 1 And
(Isnull({TableName.Sales_Date}) OR {TableName.Sales_Date} = 0) Then #1/1/1999#

Or you could simply try it without the "OR {TableName.Sales_Date} = 0" and see what you get.

If {Sales_Primary.Type_Of_Sale} = 1 And
Isnull({TableName.Sales_Date}) Then #1/1/1999#

Jason

[Former Member]

Try this

If {Sales_Primary.Type_Of_Sale} = 1 And

trim()="" Then Date("1/1/1999")

and change the option "Exception for nulls" to "Default values for nulls" in the formula editor you can see at the top.

Regards,

Raghavendra

[Former Member]

Try this instead:

If {Sales_Primary.Type_Of_Sale} = 1 And
Isnull({TableName.Sales_Date}) Then #1/1/1999#

That will catch the NULLS...

Use

If {Sales_Primary.Type_Of_Sale} = 1 And
(Isnull({TableName.Sales_Date}) OR {TableName.Sales_Date} = "") Then #1/1/1999#

if you want to catch empty strings as well.

Hope this helps,

Jason