Setting null in a function 2

[smn198]

I have two functions, 1stReminder and 2ndReminder.

What I expect to happen is as follows:
o 1stReminder - Will return the Last1stReminderDate if it is greater than the LastPaymentDate or the LastPaymentDate is null.
o 2ndReminder - Will return the Last2ndReminderDate it is greater than 1stReminder and 1stReminder is not null

1stReminder

[COLOR=blue]if[/color]
    [COLOR=green]// If there are no payments or the 1st reminder date is since the last payment date[/color]
    [COLOR=blue]isnull[/color]({Command.LastPaymentDate}) [COLOR=blue]or[/color] {Command.Last1stReminderDate} > {Command.LastPaymentDate}
[COLOR=blue]then[/color]
    {Command.Last1stReminderDate}

2ndReminder

[COLOR=blue]if[/color]
    [COLOR=green]// If the previous formula is not null and the 2nd reminder was since the 1st[/color]
    [COLOR=blue]not[/color]([COLOR=blue]isnull[/color]({@1stReminder})) [COLOR=blue]and[/color] {Command.Last2ndReminderDate} > {@1stReminder}
[COLOR=blue]then[/color]
    {Command.Last2ndReminderDate}

At the moment it is not working.
Last1stReminderDate = 17/02/2004
Last2ndReminderDate = 17/03/2004
LastPaymentDate = 16/06/2004

In the following case 2ndReminder has a value of 17/03/2004 instead of null. 1stReminder appears to be null or at least balnk. How do I make it null?

 

[IanWaterman]

I think your problem relates to not telling your first formula what to return if condition fails.

Try modifying to

if
// If there are no payments or the 1st reminder date is since the last payment date
isnull({Command.LastPaymentDate}) or {Command.Last1stReminderDate} > {Command.LastPaymentDate}
then
{Command.Last1stReminderDate}
else
date(1900, 01, 01)

and your second as

if
// If the previous formula is not null and the 2nd reminder was since the 1st
{@1stReminder} = date(1900, 01, 01)
and {Command.Last2ndReminderDate} > {@1stReminder}
then
{Command.Last2ndReminderDate}

Ian

 

[lbass]

Try changing your first formula to:

if isnull({Command.LastPaymentDate}) or
{Command.Last1stReminderDate} > {Command.LastPaymentDate} then
{Command.Last1stReminderDate} else
date(0,0,0)

Then change your second formula to:

if {@1stReminder} <> date(0,0,0) and
{Command.Last2ndReminderDate} > {@1stReminder} then
{Command.Last2ndReminderDate}

-LB

 

[smn198]

Hi Ian

That nearly works although now I get the date 1/1/1900 instead of a blank. I know I could suppress this but I would prefer if it were just blank. Is it possible to assign null? Shouldn't it be null if it is unassigned?

Thanks

Steve

 

[IanWaterman]

Try LBs solution that may be better. I suspect you will need to conditionally suppress.

Ian

 

[smn198]

Thanks Both.

lbass, that solved the display problem.

I guess it is not possible to assign or leave as null :S

 

[lbass]

Ian, I didn't see your solution before posting. This is an interesting problem. Without the suggested change to {@1stReminder}, it does not test out as null or as equal to date(0,0,0). It does have a length, so appears to retain some sort of formatting, though blank. The other approach that works is to leave {@1stReminder} as is, and change the second formula to:

if totext({@1stReminder}) <> totext(date(0,0,0)) and
{Command.Last2ndReminderDate} > {@1stReminder} then
{Command.Last2ndReminderDate}

-LB