Complex Data Test

[coldspring]

I am going on 3 days without sleep and am bleary eyed and unable to figure this one out. I need to set a flag to "Y" when two dates are greater than a test date and where the foreign key is NULL. I am using the following formula but am not getting the desired result. Am I doing something wrong with the dates?

If((Invoice Max Date>2/8/2004 and ICL Commission Header ID<1 and ICL Signoff Date>2/8/2004), "Y", "N")

[stanley]

Coldspring:

What do you get if you drop one or more of the tests? Does each one work alone? Are any of them calculations? Sorry to give you more to think about when you're exhausted, but your calc looks okay from the outside.

-Stanley

[Lee Smith]

I replaced your "Hard Coded" date of 2/8/2004 with a field "Any Date" in order to test different scenarios. I can't see anything wrong with your calculation, other than maybe it isn't unstored. Here is a case statement that seems to work.

Maybe we are not understanding what you are expecting.

Case(

Invoice Max Date>Any Date and

ICL Commission Header ID<1 and

ICL Signoff Date>Any Date, "Y", "N")

BTW, never, never, never go 3 days straight over a calculation. Much better on you health to just post your problem here.

Lee

FileMaker Version: 6

Platform: Mac OS 9

[The Shadow]

coldspring,

Date literals cannot appear directly in a calculation, use TextToDate() to make real dates:

If((Invoice Max Date>TextToDate("2/8/2004") and ICL Commission Header ID<1 and ICL Signoff Date>TextToDate("2/8/2004")), "Y", "N")

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Ugo DI LUCA]

Yes,

I was surprised Stanley and Lee could have it working without the TextToDate()...

BTW, same is for your Max Date if it is Text.

FileMaker Version: 6

Platform: Mac OS 9

[Lee Smith]

Hi Ugo,

I used a date field called "Any Date" instead of hard coding it and because it was returning what appeard to be the result that Coldspring was looking for, I didn't dig any deeper.

[coldspring]

Many thanks everybody...the TexttoDate did the trick!