Thank you for your post.
Without seeing your file and data, it is difficult to determine what is the problem. I'm not sure what you are trying to accomplish, but if I had your file, here are the steps I would take....
1. Go into Manage -> Database, click on the Relationships tab and make sure I have DESIGNATED_SUB linked to SUB1_DATA (what is the key field?), and DESIGNATED_SUB 2 linked to SUB1_DATA.
2. Separate the two criteria into separate fields. That is:
Lookup (DESIGNATED_SUB:: DESSUB_SH ) = SUB1_DATA::SUB1_ShNm
Lookup (DESIGNATED_SUB 2:: DESSUB2_SH ) = SUB1_DATA::SUB1_ShNm
3. Since there is only one evaluation, I would change the Case statement to an If statement.
Thanks for the reply.
The calculation works fine "as is" and returns the expected data unless it is initiated from a record (in SUB1_DATA) containing a date field ( not creation or modification date) earlier than the Modification Date in the matching record in the DESIGNATED_SUB table. In this context "or" is necessary, not "and". Yes, there's no reason for this to be a Case function. When I wrote it I must have been thinking there would be more than one evaluation. This is more of a mystery than a problem. The fact the function doesn't return the "*" for an "earlier" record protects the history in fact.