using a calculation that references text and dat

[Miko]

i am using a calculation to set a field with text. i use the match of this text to another relational database (this is all in fm pro 6, unfortunately) to create a relational value list. up until now, the relation has been based on text. now i need it to calculate a text field as well as if it is after a certain date. and i am not getting the right results. here it is:

If(Left(Customer ID, 5) = "Enron","Enron",

If(Customer ID = "TS&S Employee","Employee",

If(Customer ID = "TS&S General","General",

If(Customer ID = "Tower Automotive","Tower",

If(Customer ID = "Saint Vincents","St. Vincents",

If(Customer ID = "Delphi Corporation" and

Slip Date > 8/31/2006,"Delphi",

"Other"))))))

now, slip date is a date field and this calculation returns text. i have tried using texttodate and datetotext to define the contents of slip date. what i don't get is, if i search on slips greater than 8/31/06 it brings the right result, but the calculation produces some wierd stuff. i find this baffling. any ideas or inspirational words of wisdom would be greatly appreciated before i throw myself out the window

Limore

ps i know this calc is pretty ugly looking. try to overlook that aspect of it. thanks. :

[Miko]

i fixed this myself. finally

If(Left(Customer ID, 5) = "Enron","Enron",

If(Customer ID = "TS&S Employee","Employee",

If(Customer ID = "TS&S General","General",

If(Customer ID = "Tower Automotive","Tower",

If(Customer ID = "Saint Vincents","St. Vincents",

If(Customer ID = "Delphi Corporation" and

Slip Date ≥ Date(9, 1, 2006),"Delphi",

"Other"))))))

[BobWeaver]

You should have been able to use the TextToDate() function, but you would have to enclose the date in quotes like this:

...Slip Date ≥ TextToDate("8/31/2006"),"Delphi"...

Also, if you use the Case() function you can get rid of all the nested If() functions, which will make it more readable.