If statements for date comparison

[clevow]

Okay, I'm really frustrated.... I want to auto-enter the current semester into a field, given semesters whose dates are defined in an If statement. For example, if today is between 8/13/01 and 12/20/01 then the semester is "Fall 2001." This is my code:

If(Today>08/13/2001 and Today<=12/20/2001,"Fall 2001",

If(Today>12/20/2001 and Today<=05/15/2002,"Spring 2002",

If(Today>05/15/2002 and Today<=08/12/2002,"Summer 2002",

If(Today>08/12/2002 and Today<=12/18/2002,"Fall 2002",

If(Today>12/18/2002 and Today<=05/14/2003,"Spring 2003",

If(Today>05/14/2003 and Today<=08/11/2003,"Summer 2003",

If(Today>08/11/2003 and Today<=12/17/2003,"Fall 2003",

If(Today>12/17/2003 and Today<=05/13/2004,"Spring 2004",

If(Today>05/13/2004 and Today<=08/10/2004,"Summer 2004","Fall 2004")

))))))))

Obviously, this assumes I won't be using this database beyond Fall 2004.

The result when this is entered is "Fall 2004." Today is most definitely October 16, 2001. I have a field that is being populated simply by "Today" and it does return 10/16/2001.

I stripped this statement down as far as:

If(Today>8/20/2001 ,"Fall 2001","Spring 2002")

and that will return the correct result (Fall 2001). However,

If(Today>12/20/2001 ,"Fall 2001","Spring 2002")

incorrectly returns "Fall 2001" (Today is still 10/16/2001)

So I went even further....

If(10/16/2001>8/20/2001 ,"Fall 2001","Spring 2002")

correctly retuns "Fall 2001"

If(10/16/2001<12/20/2001 ,"Fall 2001","Spring 2002")

incorrectly returns "Spring 2002"

Am I missing something or is this a bug? FM should be able to handle the dates, right?

The field this is populating is set up as a text field with no validation. This is FileMaker Pro 5.5 Academic Version (should be the same as the regular version) running on Win2k.

Your help, wisdom, insight, insults are welcome and appreciated. Well, not the insults, but if I'm missing something stupid, I'll take them.

Thanks,

Carrie

[Vaughan]

First thing, don't use the Today function, use Status(CurrentDate) instead. Today only calculates when the database is opened which means if it's left open for more than 24 hours the dat will be wrong. Status(CurrentDate) can be unstored so it re-calculates whenever it's needed.

[clevow]

Okay, I'll do that. Thanks.

However, even if TODAY is really not October 16th, October 16th does come before December 20th.

Suggestions??? Thanks!

-Carrie

[elvis_impersonating_penguin]

first off, make sure all your fields that need to be dates, are date field. second, i have had a similiar problem, where the calculation would not recognize a date if i entered it like so, 08/13/2001. so what i did was entered the dates like this, Date(8 , 18, 2001). That seemed to remedy my problem.

so try changing this:

If(Today>08/13/2001 and Today<=12/20/2001,"Fall 2001",

If(Today>12/20/2001 and Today<=05/15/2002,"Spring 2002"

...

to this:

If(Today>Date(8,13,2001) and Today<=Date(12,20,2001),"Fall 2001",

If(Today>Date(12,20,2001) and Today<=Date(5,15,2002),"Spring 2002"

...

it may not work, but it is worth a try.

[clevow]

Thanks, Bman! That worked! Yay! Now I can move on to other problems..... I'm sure there will be many!