Why are you using fields of type text instead of fileds of type date? That seems to unnecessarily complicate your situation.
And have you considered using a related table for the up to 4 dates per parent records instead of 4 separate date fields?
That would also simplify things here.
I used text fields instead of date because date format for user input is just too fussy. How could I set up a related table ? I still don't see why the first If statement doesn't work.
You really, really need fields of type date. "Too fussy" has me puzzled. Data entry for fields of type date seems very straight forward to me whether you use a calendar pop up or just a straight edit box.
Without a field of type date for each date, you risk data entered that is either ambiguous or for invalid dates without any built in traps to catch the data entry errors.
Plus, text fields cannot be properly evaluated to determine which date is the most recent (What I assume you mean by "last") date.
WIth fields of type date:
Max ( DateField1 ; DateField2; DateField3; DateField4 ) --if you have individual fields
Max ( RelatedTable::DateFIeld ) -- if you use the related table I have suggested
will return the most recent date in the date fields.
To set up a related table, you need a relationship such as:
OriginalTable::__pkOriginalTableID = DatesTable::_fkOriginalTableID
You can then use a portal to DatesTable or a set of one row portals to DatesTable to display the dates on your layout.
And with a related table of dates--with fields of type Date to store the dates, either a sorted relationship or a sorted portal that sorts by Date in Descending order can also be used to display the most recent date.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained