I have the following codes that look for the common records of two tables and join the caseIDs for these common records as one string:
With CurrentDb.OpenRecordset("SELECT tbl_A.* FROM tbl_A INNER JOIN tbl_B ON " _
& "Mid(tlb_A.aFieldThatContainsCaseID, 58, 5) = tbl_B.caseID WHERE tbl_A.fld_1 IS NOT NULL")
While Not .EOF
existVal = Mid(.Fields("aFieldThatContainsCaseID"), 58, 5) & " " & existVal
But error occurs saying type mismatch. I think the problem lies in the Mid function in the SQL statement. Can anyone suggest a way out?