Ok...haha sorry for all the posts. I have tested each of these 5 statements individually and they work. The question is how do I join them together?

//if isnull({tbl_DA_Mental_Health.Data_Entry_Date})then
//    {tbl_DA_Adult_Mental_HA.Data_Entry_Date}
//else {tbl_DA_Mental_Health.Data_Entry_Date}
//

//if isnull({tbl_DA_Adult_Mental_HA.Data_Entry_Date})then
//    {tbl_adult_intake_assess_V2.Data_Entry_Date}
//else {tbl_DA_Adult_Mental_HA.Data_Entry_Date}

//if isnull({tbl_adult_intake_assess_V2.Data_Entry_Date})then
//    {tbl_adult_intake_assessment.assessment_date}
//else {tbl_adult_intake_assess_V2.Data_Entry_Date}

//if isnull({tbl_adult_intake_assessment.assessment_date})then
//    {tbl_v2_adult_intake.assessment_date}
//else {tbl_adult_intake_assessment.assessment_date}

if isnull({tbl_v2_adult_intake.assessment_date})then "MHA Missing"

Yes you are correct. the hierarchy is:
{tbl_v2_adult_intake.assessment_date}
{tbl_adult_intake_assessment.assessment_date}
{tbl_adult_intake_assess_V2.Data_Entry_Date}
{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
{tbl_DA_Mental_Health.Data_Entry_Date}

I also tried this formula and it works for the first three fields but not sure how to add in the last two.


if isnull({tbl_v2_adult_intake.assessment_date})
then
(
if isnull({tbl_adult_intake_assessment.assessment_date})
then
    {tbl_adult_intake_assess_V2.Data_Entry_Date}
else
    {tbl_adult_intake_assessment.assessment_date}
)
else
{tbl_v2_adult_intake.assessment_date};

Back to the error message.  You were checking the fields against a "", so I made the assumption the fields were strings. The issue you are going to have is the formula has to return a single data type.  It will not return a date or a string (last if statement).  My formula would work if you replaced the = '' with the isnull check (as you did in the previous post), but also for the last if statement to work where you have the phrase "MHA Missing", the result of each if statement will have to be converted to a string (i.e, CSTR or TOTEXT).