I DL'd this function from BrianDunning.com and it is pretty useful.

However, I need to establish a global variable value via an IF statement berfore the Let statement. Filemaker doesn't seem to like what I've done.

I have seen IF statements after Let but I can't get it to work BEFORE the Let or anywhere else.

What am I doing wrong?

Your let statement is off:

Let (

[

$$date2 = If ( isEmpty ( date2 ) ; get(CurrentDate) ; date2 )

];

<calc>

)

There is an error, as you describe. But even corrected, $$date2 isn't used anywhere else in the function.

So what is the purpose of the global variable?

Why not actually use the function exactly as written?

http://www.briandunning.com/cf/518

Your let statement is off:

Let (

[

$$date2 = If ( isEmpty ( date2 ) ; get(CurrentDate) ; date2 )

];

<calc>

)

Thanks for the guidance. I changed the script accordingly but still get weird results. Can you see what I am missing?

There is an error, as you describe. But even corrected, $$date2 isn't used anywhere else in the function.

So what is the purpose of the global variable?

Why not actually use the function exactly as written?

Answer: As written, when there is a value in date1 and NOT a value in date2, the outcome is just weird. My application tracks prescriptions. Some prescriptions are started but NOT ended. I want the function to calculate the outcome in this instance by subtituting get(currentdate) for date2 when date2 is empty.

http://www.briandunning.com/cf/518

// © 2006 Winfried Huslik, www.fmdiff.com

Let ( [



    now = Case ( isEmpty ( date2 ) ; Get ( CurrentDate ) ; date2 ) ;

    neg = Case ( date1 > now ; -1 ; 1 ) ;

    d1 = Case ( neg < 0 ; now ; date1 ) ;

    d2 = Case ( neg < 0 ; date1 ; now ) ;

    d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (

	  Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;

    m = mod ( Month ( d2 ) - Month ( d1 ) -

	  ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

    y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -

	  ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )

  ];



    y * neg & ¶ &

    m * neg & ¶ & 

    d * neg & ¶ &

    y & " years, " & m & " months, and " & d & " days" & ¶

)

Unless you need to use the date2 outside the function you don't need a $$variable

It would be better to leave the function as it was originally written and then when you call the function use

Age ( table::MyStartDate ; Case( isEmpty (table::MyEndDate); Get ( CurrentDate ) ; table::MyEndDate) )

Passing the logic in the function's parameter will not impact other uses of the function.

Thank you,Thank you,Thank you,Thank you,Thank you,Thank you,Thank you,!

Because of your clear and lucid example I was able to 'up my knowledge'. I now have a much better understanding of CF's.

Your suggestion to use Case in the call to the function led me to solving the problem of 'garbage' information showing when there was no StartDate...

fixed with this calculation in RX::Duration

This age function can also be wrapped with

GetValue ( Age ( Start ; End ) ; 1 ) to get the only the years

or

GetValue ( Age ( Start ; End ) ; 4 ) to only retrieve the last line or result.